SQL Server Performance Forum – Threads Archive

What are the reasons to make an index cluster?

I hope something can give a general guideline in creating a clustered index. As of now, I can only think of one: 1. To optimize queries which will perform on many rows using the columns in the clustered index. Thank you for any input, Peter

Except for the obvious like helping sorting, searching and grouping performance, Help organize data so that the rows are not thrown all over the place and hence the Read ahead can be more efficient. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

One important thing about clustered indexes is that the leaf level of such an index contains actually the data pages. Which means they are part of the index and do not only contain a pointer to the data like non clustered indexes do. So often you will read that clustered indexes are extremely useful for range queries. As every table can only have one clustered index (and almost every table should have one!), deciding on how to design this index has a serious impact on performance. I don’t think there are general guidelines for this. You have to analyse your table, the data and the queries that run against this table and then decide on which to choose. Reading carefully through BOL and MSDN and/or technet might also help. Here are some links that might get you started (although the two I was searching for, somehow got lost here. I’ll post them when I find them again) http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/itwforsql.asp
http://support.microsoft.com/default.aspx?scid=kb;EN-US;820209 HTH
–Frank
http://www.insidesql.de

http://www.sql-server-performance.com/mr_indexing.asp &http://www.sql-server-performance.com/clustered_indexes.asp for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>