tailieunhanh - Hướng dẫn học Microsoft SQL Server 2008 part 60

Chỉ số Composite Một chỉ số tổng hợp là một nhóm hoặc không tập trung chỉ số bao gồm nhiều cột quan trọng. Hầu hết các chỉ số nonclustered là chỉ số tổng hợp. Nếu bạn sử dụng SQL Server Management Studio của chỉ số tiết hình thức, chỉ số tổng hợp được tạo ra bằng cách thêm vào nhiều cột để các chỉ số trong trang General. | Part IV Developing with SQL Server By default a clustered index is created automatically when the primary key constraint is created. To remove an index use the DROP INDEX command with both the table and index name DROP INDEX p Indexes once created do not automatically maintain their efficiency. Updates can frag-j 1 ment the index and affect the index page s fill factor. While this chapter mentions index maintenance Chapter 42 Maintaining the Database details the administrative requirements for index performance. Composite indexes A composite index is a clustered or non-clustered index that includes multiple key columns. Most nonclustered indexes are composite indexes. If you use SQL Server Management Studio s Index Properties form composite indexes are created by adding multiple columns to the index in the General page. When creating a composite index with code it must be declared in a CREATE INDEX DDL statement after the table is created. The following code sample creates a composite clustered index on the Guide table in the CHA2 database CREATE CLUSTERED INDEX IxGuideName ON LastName FirstName The order of the columns in a composite index is important. In order for a search to take advantage of a composite index it must include the index columns from left to right. If the composite index is lastname firstname a search for firstname will not use the index but a search for lastname or lastname and firstname will. - - - - - SQL Server can index words within columns using Integrated Full-Text Search covered in j 1 chapter 19 Using Integrated Full-Text Search. Primary keys A primary key can be initially defined as a clustered or non-clustered index. However in order for the index type to be changed the primary key constraint must be dropped and recreated a painful task if numerous foreign keys are present or the table is replicated. For more information on designing primary keys please see the section Primary keys earlier in this .