Menu
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
662
A clustered index may be the fastest for one SELECT statement but it may not necessarily be correct choice. SQL Server indices are b-trees. A non-clustered index just contains the indexed columns, with the leaf nodes of the b-tree being pointers to the approprate data page.
By Default Primary Keys Of The Table is a Clustered Index. It can be used with unique constraint on the table which acts as a composite key. A clustered index can improve the performance of data retrieval. It should be created on columns which are used in joins.
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
Generally, nonclustered indexes are created to improve the performance of frequently used queries not covered by the clustered index or to locate rows in a table without a clustered index (called a heap). You can create multiple nonclustered indexes on a table or indexed view.
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!
168
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
