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?

  • 3
    An identity column is an integer column. – a_horse_with_no_name Nov 14 '14 at 9:25
  • I understand an identity column is a numeric, i.e. a string only composed of decimal digits. A numeric (9), for instance, would be about 9 bytes, while an int would be about 4 (actually it is 5, but you get the point). – carlo.borreo Nov 14 '14 at 9:37
  • 3
    Where did you pick up this understanding? Have you looked at the books online topic for ’identity` to see the supported datatypes? Then look at the topic for numeric. Whilst numeric(x,0) is supported this is not stored as a string. – Martin Smith Nov 14 '14 at 9:55

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.

  • Testing? Of course, but as you say, we would require it ever for just making the field longer. – carlo.borreo Nov 14 '14 at 12:43
  • I understand that I was wrong, assuming that IDENTITY fields cannot be INT. But the question can still stand: does an INT primary key perform better than a NUMERIC(10)? – carlo.borreo Nov 14 '14 at 13:16
  • For 300,000 rows the difference is practically immeasurable. If you were talking about 3,000,000,000 rows, you would want to use a BIGINT over pretty much any other data type. – Max Vernon Nov 14 '14 at 13:49
  • NUMERIC(10) would be stored as 9 bytes per value, so there will be some performance difference compared to an INT (4 bytes) and to a lesser extent BIGINT (8 bytes). This is in part because when hitting disk you are pulling more pages as the rows take more room, and in part because the CPU has more work to do as it doesn't natively support loading and comparing 9 byte values in single instructions (manipulating 8, 4 2, and 1 byte values is natively supported. Whether either difference will be visible in the performance of you r application is something you'd need to benchmark to be sure. – David Spillett Nov 14 '14 at 15:17

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.