Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Robin Schumacher

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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter