One of the colleagues asked me question which one is better clustered
index or non-clustered index in Sql server?
I didn’t understand the question exactly. I thought both clustered and
non-clustered indexes are two different objects and both of them have their own
advantages and disadvantages. How can I say which one is better? At first
glance this question looked like me some one is asking pen is better or copy?
It doesn’t make any sense.
After couple of questions he corrected his question. His actual question
was for a given SELECT statement which index performs better clustered or
non-clustered?
Now I got the question. Answer is pretty simple. It depends upon on the
SELECT statement. Let me elaborate my answer.
Case 1: If SELECT statement cannot use indexes.
If SELECT statement has not any filtered condition or if it has such
condition which cannot use indexes (for example <>, OR, LIKE ‘%Exp%’
etc), not order by clause etc in that case no index may be better for that
query neither clustered index nor non-clustered index.
Case 2: If SELECT statement can use index but table has already
clustered index on different key column.
Since a table can have only one clustered index so there is not any
scope of other clustered index for the SELECT statement. In this case
non-clustered may be better for the SELECT statement.
Case 3: If SELECT statement can use index but table has not any
clustered index.
Again there are too many cases to decide this. If filter condition is on
unique column with equality operator in that case clustered index may perform
better, if number of columns in a table is very larger and the query need only
couple of columns then non-clustered index may be better since it will pull
less numbers of data\index pages etc.
No comments:
Post a Comment