Record ID's

[james.harper]

At the conference, it was mentioned that each table would have a column called 'RecID' as the primary key. Will this be a string or a 'uniqueidentifier'? The former is currently 20 or 36 bytes, while the latter is 16 bytes. I would like to see Jiwa use 'uniqueidentifier' fields as they are better form a performance point of view.

[Mike.Sheen]

At the moment we've just got CHAR(36) fields as the RecID data types, but we will change to uniqueidentifier's, with a default constraint of NewID() - or perhaps NewSequentialID

We were hoping to go to int types, but the technique of generating new, unique values - (ie : IDENTITY constraints) is horrible to work with.

[Mike.Sheen]

Ok, today I've laid the groundwork for this, and UniqueIdentifier datatypes are in.

So, most tables will look something like this :

CREATE TABLE IN_Main
(
RecID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),
... // some more fields
LastSavedDateTime DATETIME NOT NULL,
RowHash TIMESTAMP NOT NULL
)

We are making use of the OUTPUT clause in the insert syntax for SQL 2005/2008 to read back the generated ID on insert.. so the actual SQL we issue looks something like this :

INSERT INTO IN_Main(PartNo, Description, ..., LastSavedDateTime)
OUTPUT Inserted.RecID
VALUES(@PartNo, @Description, ... GetDate())

What this means, is - as James quite rightly pointed out - is reduced row size and increased performance.

Anyone worried about how one writes queries in TSQL for UniqueIdentifier column literals should be able to rest at ease : you can provide literals easy enough..

eg :

SELECT * FROM IN_Main WHERE RecID = '6F9619FF-8B86-D011-B42D-00C04FC964FF'

works fine, as does :

SELECT * FROM IN_Main WHERE RecID = 0x6F9619FF8B86D011B42D00C04FC964FF

The above two queries are both valid, and would return the same row - just the latter uses the binary literal and the former uses a string literal. At the end of the day, the data stored is a 16 byte binary value - MS was just kind enough to allow queries to provide either a string or binary literal.