What goes on if your clustered index isn't unique? Will it result in bad performance because placed rows flow for an "overflow" page of some sorts?
Could it be "made" unique and when just how? What's the easiest method to allow it to be unique?
I'm asking because I'm presently utilizing a clustered index to split my table in logical parts, however the performance is really-so, and lately I acquired the advice to create my clustered indexes unique. I would like another opinion on that.
They do not have to become unique but it definitely is urged.
I've not experienced a predicament yet where I needed to produce a CI on the non-unique column.
What goes on should you create a CI on a non-unique column
When the clustered index isn't a unique index, SQL Server makes any duplicate secrets unique with the addition of an internally produced value known as a uniqueifier
Performs this result in bad performance?
Adding a uniqueifier certainly adds some overhead in calculating as well as in storing it.
If the overhead is going to be noteworthy is dependent on several factors.
- The number of data does the table contain.
- What's the rate of Card inserts.
- How frequently may be the CI utilized in a choose (when no covering indexes exist, virtually always).
as been stated by Remus in comments, there are available use cases when developing a non-unique CI will be a reasonable choice. Me not getting experienced one-time individuals situations basically shows my very own insufficient exposure or competence (pick your decision).
Non-unique CI are very common really. Whenever the prevalent access pattern comes from queries that request range scans on the certain column, making the column the clustered index key makes lots of sense. The very best example is really a DATE column on the logging table.
Ultimately that internally the engine adds an 'uniquefier' hidden column towards the clustered index. Klaus Aschenbrenner has a number of articles that demonstrate at length the salt water evaporates:
I love to take a look at exactly what the Full of Indexing, Kimberly Tripp, needs to say around the subject:
I am going to begin with my recommendation for that Clustering Key - for a few reasons. First, this is an easy decision to create and 2nd, causeing this to be decision early helps you to proactively prevent some kinds of fragmentation. If you're able to prevent certain kinds of base-table fragmentation you'll be able to minimize some maintenance activities (most of which, in SQL Server 2000 And fewer which, in SQL Server 2005) require that the table be offline. OK, I'll arrive at the rebuild stuff later.....
Let us begin with the important thing stuff that I search for inside a clustering key:
* Unique * Narrow * Static
Why Unique? A clustering key ought to be unique just because a clustering key (when one is available) can be used because the research key all non-clustered indexes. Take for instance a catalog at the back of a magazine - if you want to discover the data that the index entry ways to - that entry (the index entry) should be unique otherwise, which index entry will be the one you are searching for? So, whenever you produce the clustered index - it should be unique. But, SQL Server does not require that the clustering secret is produced on the unique column. You may create it on any column(s) you would like. Internally, when the clustering secret is not unique then SQL Server will “uniquify” it with the addition of a 4-byte integer towards the data. Therefore if the clustered index is produced on something which isn't unique then not just can there be additional overhead at index creation, there's wasted disk space, additional costs on Card inserts and UPDATEs, as well as in SQL Server 2000, there's an additional cost on the clustereD index rebuild (which due to poor people option for the clustering secret is now much more likely).