NewID() vs NewSequentialID() in SQL Server

GUID (Global Unique Identifier) are used as PK (primary keys) when developers need a key that would be unique across multiple database servers. While the GUID has this benefit, there is a drawback to it. There is a performance drawback because of the size and non-sequentialness of the GUID. So what is the solution if you really need a GUID so to use a unique id accross multiple database servers? You can use NewSequentialID().

The NewSequentialID() is new to SQL Server 2005. It generates a sequential number based on the last one that was created. This improves performance but if you don't need such a GUID then it is better to use an autogenerated INT.

To see what the unique IDs look like use this:

-- Creating a local variable with DECLARE/SET syntax.
DECLARE @myid uniqueidentifier
SET @myid = NEWID()

PRINT 'The value of @myid is: '+ CONVERT(varchar(255), @myid)

You should see something like this generated: 4F375C27-90A7-41B8-A073-56769D528DA0


To use NewID or NewSequentialID() in creating your tables use this example:

CREATE TABLE Test1 (
customerID uniqueidentifier DEFAULT NewID()
)

--you need at least SQL Server 2005 for this
CREATE TABLE Test2 (
customerID uniqueidentifier DEFAULT NewSequentialID()
)

INSERT INTO Test1 DEFAULT VALUES
INSERT INTO Test1 DEFAULT VALUES

INSERT INTO Test2 DEFAULT VALUES
INSERT INTO Test2 DEFAULT VALUES


SELECT customerID, CONVERT(binary(16), customerID) AS ConvertBinary
FROM Test1

SELECT customerID, CONVERT(binary(16), customerID) AS ConvertBinary
FROM Test2

You should be able to see a sequence number for Test2 data as we used the NewSequentialID().



Reference:
- http://www.sqljunkies.com/Article/4067A1B1-C31C-4EAF-86C3-80513451FC03.scuk
- http://msdn2.microsoft.com/en-us/library/ms190348.aspx
- http://www.sql-server-performance.com/articles/per/guid_performance_p1.aspx