3

I'm going to add a clustered index to a fairly large table:

  • over 7 million records
  • 106 columns
  • no PK
  • no other indexes.

There is an Id column which could potentially be used for the index key, but it's nvarchar(18) and not unique (although there are no duplicate values there).

I'm however reluctant to use it because of its datatype (performance impact) and have been thinking of adding an int identity(1,1) column and use it as the clustered index key.

Which solution would you rather recommend? TIA

3
  • 1
    How is the table used, I.e. queried? One-row selects? Only as the result of a join? Selects based on a set of columns the table already has? – Paul Holmes Apr 27 '20 at 13:03
  • how do you insert data into the table? – NikitaSerbskiy Apr 27 '20 at 18:18
  • The table is used for reporting, pretty much always joined with other - usually large - data sets. Data is batch-loaded every 0,5 h - several dozen of records every time. Like I said - the table has no indexes at all. I'm going to create ones - the clustered index is just a starting point. – Timbalero Apr 27 '20 at 19:05
6

Have been thinking of adding an INT identity(1,1) column and use it as the clustered index key

Yes, this is a way to go
Why - read below:

Requirements for good clustered index:

1) Ever Increasing
This will help to reduce index fragmentation in future

2) Unique
If Clustered index key rows are not unique, additional hidden INT uniquifier (4 bytes) is added by SQL Server automatically to make clustered index keys unique, and that increases storage requirements both for clustered index, and non-clustered indexes (they are affected by clustered index width, too, since they include hidden copy of clustered index key)

3) Narrow
Choose as small data type as possible, because again it increases storage and RAM requirements, and lowers performance

This is why Id nvarchar(18) that you have, is bad candidate for a clustered index key, so go ahead and add INT identity(1,1) column

6

In general, a surrogate ID (an incrementing number that doesn't have any other meaning) is the best choice unless you have a specific reason why it won't work.

One of the biggest problems with using a non-incrementing value for a primary key is that you'll have page splits on some inserts, which will cause them to be slower. How much slower depends on a lot of factors, so it's just something you'd have to measure on the system.

Google "benefits of surrogate primary key" and you'll find a ton of information. It's really a small book to cover the topic, so the full answer isn't really appropriate for this site. This question also has a few answers that provide good information on this topic.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.