SQL Data Type for primary key in format of a version number

Question

The table I am working on has a primary key which is essentially a version number. A couple of examples of the version number are below:

1.0.0.0
1.1.2.24

Varchar seemed the most obvious choice to me, but I'm looking to set the primary key as a clustered index - I have read on SQL performance sites that you should avoid using varchars in clustered primary keys due to paging issues.

Given the version number is of variable length, what would therefore be the most appropriate datatype for the column?

This database I'm working on is running SQL Server 2005.

Thanks for your help.

Answer 1

Well, you could always use four INT fields - MAJOR, MINOR, RELEASE and BUILD - together, those are 16 bytes like a GUID - not too bad.

The only drawback to this approach is that if you need to reference this table in a foreign key constraint, you'll have to join on all four tables, typically.

I wouldn't use VARCHAR - INT is better (typically faster). Also, due to the variable nature of VARCHAR, things can get a bit messy (VARCHAR fields can grow and cause all sorts of issues, while an INT always stays 4 bytes).

The other quite popular option would be to have those four fields for the actual version, and an additional surrogate VersionID INT IDENTITY for optimal clustered key performance.

Answer 2

You could introduce a surrogate key (e.g. an IDENTITY int value) and place a unique constraint on the version number column.

Or it may be that the performance issues aren't a significant overhead in your situation, and you can keep the version number as a primary key.

Answer 3

How strict is the format requirement? If it must be "1.0.0.0" then you really only have varchar and char as options --- but that's only the beginning of your problems ("1.0.0.10" will sort before "1.0.0.2")

If you can force a fixed size, say "001.000.000.010", than you could use a char(15) field which should your problems.