SQL Server: performance of IDENTITY vs INT as primary key

Posted on

Question :

The most important table of my database is about 300,000 records, and growing. More than 20 tables have a FK to it.
Its primary key is a number, but for historical reasons is defined as nvarchar(6) which is obviously inefficient (and ugly).
Records are sometimes deleted from the record, so the primary key is approaching 999,999 and I must change it.
An identity field would be the obvious choice.
An int key, or similar, with the increment produced either by a trigger or by the software, would be an alternative. This would be feasible because the records are always inserted one at a time.
Would an int key provide better performances with complex queries?

Answer :

IDENTITY is not a column type in its own right, it is a property that can be apply to any integer column type. It is usually applied to an INT and in that case there is no difference (the column is an INT column, with the IDENTITY property defined) though it can be applied to any type that is internally an integer (BIGINT, DECIMAL, MONEY, …).

While your string type is ugly and an integer would be measurably more efficient, changing to a numeric type will require quite some testing effort to make sure that none of your application’s code has oddities that make it sensitive to the data types of these keys. I strongly recommend you don’t just make the change and hope for the best! Even just extending the keys to NVARCHAR(10) or such might be problematical if any code assumes that the keys will never be longer than six characters… If you don’t switch to a numeric type, at least consider moving to CHAR() – a non varying string format will also be more efficient and if you are only storing numbers you don’t need the two-bytes-per-character storage of a unicode type like N[VAR]CHAR.

Leave a Reply

Your email address will not be published. Required fields are marked *