Database Reference
In-Depth Information
Figure 8-16. Execution plan using both columns
The radical changes in I/O and execution plan represent the real use of a compound index, the covering index.
This is covered in detail in the section “Covering Indexes” in chapter 9.
When finished, drop the index.
DROP INDEX Person.Address.IX_Test;
Consider the Type of Index
In SQL Server, from all the different types of indexes available to you, most of the time you'll be working with the two
main index types: clustered and nonclustered. Both types have a B-tree structure. The main difference between the
two types is that the leaf pages in a clustered index are the data pages of the table and are therefore in the same order
as the data to which they point. This means the clustered index is the table. As you proceed, you will see that the
difference at the leaf level between the two index types becomes important when determining the type of index to use.
Clustered Indexes
The leaf pages of a clustered index and the data pages of the table the index is on are one and the same. Because of
this, table rows are physically sorted on the clustered index column, and since there can be only one physical order of
the table data, a table can have only one clustered index.
When you create a primary key constraint, SQL Server automatically creates it as a unique clustered index on the
primary key if one does not already exist and if it is not explicitly specified that the index should be a unique nonclustered
index. this is not a requirement; it's just default behavior. You can change the definition of the primary key prior to
creating it on the table.
Tip
Heap Tables
As mentioned earlier in the chapter, a table with no clustered index is called a heap table. The data rows of a heap
table are not stored in any particular order or linked to the adjacent pages in the table. This unorganized structure
of the heap table usually increases the overhead of accessing a large heap table when compared to accessing a large
nonheap table (a table with a clustered index).
 
 
Search WWH ::




Custom Search