SQL Server - When to use Clustered vs non-Clustered Index?

Question

I know primary differences between clustered and non clustered indexes and have an understanding of how they actually work. I understand how clustered and non-clustered indexes improve read performance. But one thing I am not sure is that what would be the reasons where I would choose one over the other.

For example: If a table does not have a clustered index, should one create a non-clustered index and whats the benefit of doing

Amoebaean

Thanks for responding. I am already aware of that. My question is would there be a situation where creating a non-clustered index would benefit more than creating a clustered index – Amoebaean

Yes, you should avoid a clustered index when; the column has low cardinality, no particular order, frequently updated, non-sequential, it is a composite of many columns... – Westland

Perhaps this question would help you: #5071029 – Heliostat

Possible duplicate of What column should the clustered index be put on? – Evania

dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html – Munafo

Answer 1

I just want to put in a word of warning: please very carefully pick your clustered index! Every "regular" data table ought to have a clustered index, since having a clustered index does indeed speed up a lot of operations - yes, speed up, even inserts and deletes! But only if you pick a good clustered index.

It's the most replicated data structure in your SQL Server database. The clustering key will be part of each and every non-clustered index on your table, too.

You should use extreme care when picking a clustering key - it should be:

• narrow (4 bytes ideal)

• unique (it's the "row pointer" after all. If you don't make it unique SQL Server will do it for you in the background, costing you a couple of bytes for each entry times the number of rows and the number of nonclustered indices you have - this can be very costly!)

• static (never change - if possible)

• ideally ever-increasing so you won't end up with horrible index fragmentation (a GUID is the total opposite of a good clustering key - for that particular reason)

• it should be non-nullable and ideally also fixed width - a varchar(250) makes a very poor clustering key

Anything else should really be second and third level of importance behind these points ....

See some of Kimberly Tripp's (The Queen of Indexing) blog posts on the topic - anything she has written in her blog is absolutely invaluable - read it, digest it - live by it!

• GUIDs as PRIMARY KEYs and/or the clustering key
• The Clustered Index Debate Continues...
• Ever-increasing clustering key - the Clustered Index Debate..........again!
• Disk space is cheap - that's not the point!

Emblazonment

Thanks marc_s for the clear explanation. But technically speaking, what does non clustered index do for us. what are its importance.. ? – Selfmoving

@nad: a well chosen non-clustered index can speed up your searches - instead of having to compare potentially millions of rows of data with your search criteria, you can find a given row with just 4, 5 comparisons. That makes a HUGE difference! – Emblazonment

@MuriloKunze: read Kim Tripp's blog post on the topic - she explains in great detail why this is the case – Emblazonment

I am sorry, but where is the answer to the question "SQL Server - When to use Clustered vs non-Clustered Index?" ? – Sather