Wednesday, January 9, 2008

Starting to Use GUIDS in SQL

When you start to use GUIDs (uniqueidentifiers) as primary keys in T-SQL one of the first questions is how to replace @@IDENITY in your primary key column.  A typical table with int as a primary key might look like this:

CREATE T1(ID int PRIMARY KEY IDENTITY, Name varchar(50))

When you insert into this table and want the primary key of the row that you inserted you typically do this:


INSERT INTO T1 (Name) values ('Tom Smith')


However, when you use uniqueidentifiers as primary keys you don't declare them as IDENTITY, here is how you would build this table:

CREATE T1(ID uniqueidentifier PRIMARY KEY , Name varchar(50))

And here is what the INSERT looks like:

DECLARE @Id uniqueidentifier

SET @Id = NewId()

INSERT INTO T1 (ID, Name) VALUES (@Id, 'Tom Smith')

When using uniqueidentifiers you create them ahead of the insertion and SQL Server doesn't keep track of the process for you.


No comments:

Post a Comment