Dec 28, 2014

Which one is better clustered index or non-clustered index in Sql server?

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