SQL Server Performance Forum – Threads Archive

clustered index NOT on base table

Hi – I need some clarification here please. One developer here has created a table with a non-clustered key. He then goes and creates a clustered index based on another column. Does thi smean that the sorted clustered index is the secondary and that the base index is non sorted? Also if the base table did not have any key at all – ie heap then the sorted index is the one clustered ie the secondary index is clustered not the base – right? Does this defeat the purpose as there will be a bookmark lookup using the clustered index and going to the base table will still be unordered.? thanks

No, the clustered index determines how the table is "logically sorted". By default, SQL Server will make the PRIMARY KEY also the clsutered index, when at that point of time, no other clustered index is yet created. But it is perfectly valid, to have a PRIMARY KEY that is not the clustered index. In a heap there is no clustered index present. That means there is no meaningful sorting anyway. Any nonclustered index on that table will use bookmark lookups to to fetch the data from the base table (unless we don’t speak of covering indexes here, of course). It will probably get clearer when you post your table structure and the indexes currently present. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

]]>