retrieve the GUID prior to insertion or use the SQL Server 2005 OUTPUT clause).
Unlike IDENTITY, a DEFAULT constraint must be used to assign a GUID value to the column. NEWID() randomly generates a guaranteed unique value based on the identification number of the server's network card plus a unique number from the CPU clock.
In contrast, NEWSEQUENTIALID() generates these values in sequential order as opposed to randomly.
I'm designing a table and I've decided to create an auto-generated primary key value as opposed to creating my own scheme or using natural keys.
I see that SQL Server offers globally unique identifiers (GUIDs) as well as identities to create these values. Yes, there are a number of ways you can auto-generate key values for your tables.
Let's create new tables that use a uniqueidentifier along with both NEWID() and NEWSEQUENTIALID() USE MSSQLTIPSGOCREATE TABLE NEWID_TEST(ID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY, TESTCOLUMN CHAR(2000) DEFAULT REPLICATE('X',2000))GOCREATE TABLE NEWSEQUENTIALID_TEST(ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY, TESTCOLUMN CHAR(2000) DEFAULT REPLICATE('X',2000))GO-- INSERT 1000 ROWS INTO EACH TEST TABLE DECLARE @COUNTER INTSET @COUNTER = 1WHILE (@COUNTER As you can see, the first table which uses NEWID() generates random values while the second table that uses NEWSEQUENTIALID() generates sequential values.As opposed to the integers generated by the IDENTITY approach, the GUID values generated are not as friendly to look at or work with. SQL Server keeps the last generated identity value in memory which can be retrieved right after an INSERT using SCOPE_IDENTITY(), @@IDENTITY, or CHECK_IDENT (depending on the scope you require).