SQL Server - creating a clustered index in a large heap - should I add a column?

Question

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

Answer 1

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

Answer 2

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.