Why non-clustered index is fast?

Question

hi Why non-clustered index is faster then the clustered index? please help me Thanks for everyone.

Bikesh Srivastava · Answer

Hi Ashok A clustered index determines the order in which the rows of the table will be stored on disk – and it actually stores row level data in the leaf nodes of the index itself. A non-clustered index has no effect on which the order of the rows will be stored. Using a clustered index is an advantage when groups of data that can be clustered are frequently accessed by some queries. This speeds up retrieval because the data lives close to each other on disk. Also if data is accessed in the same order as the clustered index the retrieval will be much faster because the physical data stored on disk is sorted in the same order as the index. A clustered index can be a disadvantage because any time a change is made to a value of an indexed column the subsequent possibility of re-sorting rows to maintain order is a definite performance hit. A table can have multiple non-clustered indexes. But a table can have only one clustered index. Non clustered indexes store both a value and a pointer to the actual row that holds that value. Clustered indexes don’t need to store a pointer to the actual row because of the fact that the rows in the table are stored on disk in the same exact order as the clustered index – and the clustered index actually stores the row-level data in it’s leaf nodes. And A clustered index would be the fastest for that SELECT  but it may not necessarily be correct choice. A clustered index determines the order in which records are physically stored (which is why you can only have one per table). So while it would be the fastest for THAT query it may slow down other queries and could KILL updates and inserts if one of those columns was changing which could mean that the record would need to be physically re-located. An INCLUDE would again speed up that query at the expense of extra storage and extra index maintenance if any of those fields (including the included fields) were updated. I would START with a non-clustered index on a b and c and see if that gets your performance to a reasonable level. Anything more could just be trading speed in one area for slowness in another.

Midhun T P · Answer

Hi Below link will be helpful for you. http://stackoverflow.com/questions/27193877/why-nonclustered-index-scan-faster-than-clustered-index-scan