February 16, 2011 at 1:11 am
Suvendu (2/15/2011)
A clustered index is a special type of index that reorders the way records in the table are physically stored.
The clustered index enforces the logical storage order of the actual rows of the table, not the physical order. SQL will try, on create or rebuild, to make the two as similar as possible, but it is unlikely that they will remain that way
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.
A nonclustered index is a separate structure from the table, the nonclustered index key enforces the logical order of the index rows, not the physical order.
p.s. this thread is 4 years old.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 10, 2011 at 3:58 am
Aaron Ingold (10/3/2007)
Boy, that's a question that could start forum wars... 😉First thing you have to understand is the difference between a clustered and nonclustered index. Clustered indexes define the physical sort order on disk of the whole table. Nonclustered indexes are seperate but connected to the table and store a small subset of the table, with pointers back to the location of the main table data.
More often than not, the clustered index is going to have better performance when it is used. Not always, because I've seen fully covered queries by nonclustered indexes that were able to be fulfilled faster using a more narrow (meaning fewer bytes allocated to columns) than by a full clustered index scan. But you want to have your clustered index cover the most commonly scanned, data. Usually that's also your primary key (which is why a clustered index is created by default on a PK in SQL Server), though not always.
Hi Aaron Ingold;
Does Clusterd index define the physical sort order?
Pls see the attached file(93.pdf) of MS press book's page of 'Self Passed TrainingKit Exam70-432'
Please clear my doubt.
Ali
MCTS SQL Server2k8
October 10, 2011 at 8:31 am
Ali Tailor (10/10/2011)
Does Clusterd index define the physical sort order?
No, it does not. It defines the logical sort order.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply