Database Reference
In-Depth Information
To understand the effect of a wide clustered index on a nonclustered index, modify the data type of the clustered
indexed column c2 from INT to CHAR(500) .
DROP INDEX dbo.Test1.iClustered;
ALTER TABLE dbo.Test1 ALTER COLUMN C2 CHAR(500);
CREATE CLUSTERED INDEX iClustered ON dbo.Test1(C2);
Running the query against sys.dm_db_index_physical_stats again returns the result in Figure 8-20 .
Figure 8-20. Number of index pages for a wide index
You can see that a wide clustered index increases the width of the nonclustered index row size. Because of the
large width of the nonclustered index row, one 8KB index page can't accommodate all the index rows. Instead, two
index pages will be required to store all 20 index rows. In the case of a large table, an expansion in the size of the
nonclustered indexes because of a large clustered index key size can significantly increase the number of pages of the
nonclustered indexes.
Therefore, a large clustered index key size not only affects its own width but also widens all nonclustered indexes
on the table. This increases the number of index pages for all the indexes on the table, increasing the logical reads and
disk I/Os required for the indexes.
Rebuild the Clustered Index in a Single Step
Because of the dependency of nonclustered indexes on the clustered index, rebuilding the clustered index as separate
DROP INDEX and CREATE INDEX statements causes all the nonclustered indexes to be rebuilt twice. To avoid this,
use the DROP_EXISTING clause of the CREATE INDEX statement to rebuild the clustered index in a single atomic step.
Similarly, you can also use the DROP_EXISTING clause with a nonclustered index.
It's worth noting that in SQL Server 2005 and newer, when you perform a straight rebuild of a clustered index, you
won't see the nonclustered indexes rebuilt as well.
Where Possible, Make the Clustered Index Unique
Because the clustered index is used to store the data, you must be able to find each row. While the clustered index
doesn't have to be unique purely in terms of its definition and storage, if the key values are not unique, SQL Server
would be unable to find the rows unless there was a way to make the cluster uniquely identify the location of each
discrete row of data. So, SQL Server will add a value to a nonunique clustered index to make it unique. This value is
called a uniqueifier . It adds to the size of your clustered index as well as all nonclustered indexes, as noted earlier.
It also means a little bit of added processing to get the unique value as each row gets inserted. For all these reasons,
it makes sense to make the clustered index unique where you can. This is a big reason why the default behavior for
primary keys is to make them a clustered index.
You don't have to make the clustered index unique. But you do need to take the uniquifier into account when
you're defining your storage and indexes.
 
Search WWH ::




Custom Search