Lets say for example that I have a table structured this way:

ID1 int
ID2 int
ID3 int
DTM datetime

I'm only able to create a unique clustered index on the table if I include ID1, ID2, and ID3 (a combination of two of them or individually would lead to duplicate rows).

Would it be better performance wise to create a clustered index by ID1, ID2, and ID3? Or create a clustered index on just ID1 and have SQL Server add a uniquifier to the index to make each row unique?

I know that the uniquifier is 4 bytes in size (same for ints) so technically it would save 4 bytes per row if I make the latter index, but I'm not sure how this would affect my queries.

  • I'm not sure I understand how sorting can change uniqueness. It's the combination that makes a row unique, not how it's ordered. – Aaron Bertrand Jan 6 '15 at 22:51
  • @AaronBertrand Sorry, I didn't mean to use the word sorting. I meant I'm not able to create a unique clustered index with UNIQUE on if I choose only 1 of the columns or a combination of 2 of the columns. – John Odom Jan 6 '15 at 22:53
  • When considering the impacts an index has on a query, also consider the impact it has on updates. In a clustered index, the table is physically arranged on disk by order of the cluster key. If a unifier is randomly generated, does it change during an update? If so, will rows get "moved around" on disk? What if you add duplicate rows? I'm not smart enough to answer these questions, but wise enough to ask them. – Brandon Jan 7 '15 at 3:25

One of the most important considerations for a clustered index in terms of performance is that it be ever increasing (or decreasing) and not something that will be changed (except possibly very rarely). The clustered index represents the physical order of your table. If you are constantly inserting into the middle of the index, or modifying the values of your clustered index then you will get bad page splits where SQL has to move part of the data from one page and move it into another one in order to make room for the new data. These moves take time and cause fragmentation that degrades performance.

That being said the size of the clustered index, while important, is not your biggest concern. I did some experiments with using date columns (with a uniqueifier) vs an int column and found that if my queries were date based I still got a big bump in performance.

My suggestion to you is that if ID1, ID2 and ID3 are ever increasing (and rarely change) then use that as your clustered index. If not, and you still want to enforce uniqueness then either make them a non-clustered primary key or a non-clustered unique key. If they are not ever increasing then you can consider your date column or just create a surrogate key for the table. Use an int data type and make it an identity column.

If you do create a surrogate key then you can create a non-clustered index on ID1 to improve the performance on those queries assuming you didn't already create a unique index for it. If you frequently need to return, say ID2 and DTM then you could INCLUDE those in your index to additionally improve your performance.


Is the proposed index meant to enforce the data constraint, or improve performance? If you want the index to prevent duplicate records, you're obliged into include all three fields.

However, if you're trying to improve performance, and your queries typically filter on ID1, you have another option: create a non-unique index on ID alone. This would be narrower than either unique index, and the performance of a non-unique index should not be perceptibly less.

If you'd like to share some sample queries, we can offer more specific advice.

  • My queries usually filter on ID1. I was taught that every table needs a clustered index, so it's okay to have the non clustered index? – John Odom Jan 7 '15 at 0:26
  • @JohnOdom Non-clustered indexes are very common. Tables can have as many indexes as you want, but can only have at most one clustered index. Therefore, any other indexes must be non-clustered. – Brandon Jan 7 '15 at 3:27
  • @JohnOdom: That's a good rule of thumb, but not an absolute. Clustered indices are most useful when you're querying ranges; for example, an Orders table may have a clustered index on OrderDate to support monthly aggregation, but a non-clustered PK on OrderID for referential integrity and quick lookups. Heap tables have their place (dba.stackexchange.com/questions/28370/…, stackoverflow.com/questions/1341393/…). – Jon of All Trades Jan 7 '15 at 15:21

Your Answer

