SQL 2005+

Generating Globally Unique Identifiers (GUIDs) in SQL Server

by Richard Carr, published at http://www.blackwasp.co.uk/SQLGuids.aspx

In an earlier article I described some of the purposes and details of globally unique identifiers and their use within the C# programming language. When storing GUIDs in SQL Server databases, the server itself can be used to generate these unique IDs.

The NewId Function

SQL Server provides the UNIQUEIDENTIFIER data type for the storage of large, globally unique numbers. The data type actually holds information that is designed to be created and viewed using the standard 32-digit hexadecimal GUID format.

Specific GUID values can be assigned directly to variables and stored in UNIQUEIDENTIFIER columns by providing the required value delimited by apostrophes ('). However, when creating new globally unique primary key values, it can be useful to assign a randomised GUID. To generate such a value, the NewId function is called. This creates a new, random value with an incredibly small chance of ever repeating a previously generated ID.

The NewId function can be used within standard T-SQL statements and is particularly useful to use as the default value for a unique column. The following example demonstrates the creation of a randomised unique identifier. NB: The output value will vary from that displayed below.

DECLARE @id UNIQUEIDENTIFIER
SET @id = newid()
PRINT @id			-- Outputs '1548C3E02-2D73-4244-8787-D45AC590519A'

The NewSequentialId Function

In SQL Server 2005, Microsoft introduced the NewSequentialId function. This function may only be used within the default constraint for a table column. It allows the creation of GUIDs that are less randomised that those created by the NewId function. Each generated GUID is guaranteed to have a higher value than the one that was previously generated on the same server.

The NewSequentialId function should provide improved performance over NewId, particularly when the generated values are indexed. However, this is at the cost of reduced security in cases where the GUIDs are publicly visible as it is possible to guess the values of other GUIDs that have been used.