| |
 |
|
SQL Server Tips by Burleson |
Clustered Indexes
There is plenty of debate on whether every table should have a
clustered index. Many SQL Server gurus suggest that each table
should indeed have a clustered index. But, how can one know what
series of columns are best suited for a clustered index?
Since clustered indexes sort the underlying table data in the order
defined in the index, columns that are the targets of queries that
routinely return single or few rows should not be indexed. Instead,
a clustered index should be created on one or more columns that are
the object of range searches, because the requested data will be
found on the same page or pages within close proximity. The end
results of smart clustered index designs are reduced I/O and better
query response times for queries that return a range of data.
Another benefit of clustered indexes is that they assist in
reorganizing a table. Unlike other database engines, SQL Server has
no command for reorganizing a heap table. It only works on indexes.
So, if there is a badly fragmented table, the DBA is usually stuck
with executing a table purge, copying all the data out, truncating
the table, and copying all the data back in. This feat is not easy
to accomplish in an environment with high amounts of enforced
referential integrity.
If the table has a clustered index, the DBA can simply rebuild the
clustered index, which reorganizes the data pages because the leaf
level of the clustered index is the data.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
