indexing - SQL Server - When to use Clustered vs non-Clustered Index? -
i know primary differences between clustered , non clustered indexes , have understanding of how work. understand how clustered , non-clustered indexes improve read performance. 1 thing not sure reasons choose 1 on other.
for example: if table not have clustered index, should 1 create non-clustered index , whats benefit of doing
i want put in word of warning: please very carefully pick clustered index! every "regular" data table ought have clustered index, since having clustered index indeed speed lot of operations - yes, speed up, inserts , deletes! if pick good clustered index.
it's most replicated data structure in sql server database. clustering key part of each , every non-clustered index on table, too.
you should use extreme care when picking clustering key - should be:
narrow (4 bytes ideal)
unique (it's "row pointer" after all. if don't make unique sql server in background, costing couple of bytes each entry times number of rows , number of nonclustered indices have - can costly!)
static (never change - if possible)
ideally ever-increasing won't end horrible index fragmentation (a guid total opposite of clustering key - particular reason)
it should non-nullable , ideally fixed width -
varchar(250)makes poor clustering key
anything else should second , third level of importance behind these points ....
see of kimberly tripp's (the queen of indexing) blog posts on topic - has written in blog absolutely invaluable - read it, digest - live it!
