This topic provides examples of how to work with primary key columns that store sequential GUIDs generated by the MS SQL server.
SQL Server allows you to create primary keys of type uniqueidentifier (GUIDs). When you have a GUID column as a row identifier, you could create a DEFAULT constraint that uses the NEWSEQUENTIALID() function. The NEWSEQUENTIALID() function creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range. Using the NEWSEQUENTIALID() function can be faster than using the NEWID() function. This is because the NEWID() function causes random activity and uses fewer cached data pages. Using the NEWSEQUENTIALID() function also helps to completely fill the index and data pages. The NEWSEQUENTIALID() function is a wrapper over the Windows UuidCreateSequential function.
For example, the following script creates a new table with a GUID column as a row identifier, and a DEFAULT constraint that uses the NEWSEQUENTIALID() function.
CREATE TABLE Products ( Id uniqueidentifier DEFAULT newsequentialid() NOT NULL, NAME varchar(255) NULL, CONSTRAINT [pk_product] PRIMARY KEY (Id) ) GO
Suppose, you have a database containing a table with a row identifier of type uniqueidentifier and that column uses a mechanism for generating a default value (either NEWSEQUENTIALID() or NEWID()). In this case, the generated domain class will have its Identity Mechanism set to DatabaseServerCalculated.
If a table has a composite primary key and one of its members uses NEWSEQUENTIALID() or NEWID() for default value, the generated class for that table will have Identity Mechanism set to default.
If your domain class has a Guid identity and its Identity Mechanism property is set to DatabaseServerCalculated, the generated DDL script will add a default constraint over the corresponding uniqueidentifier column. The added constraint will use the NEWSEQUENTIALID() function to generated default values for the row identity column.
In case your class has Composite Identity, in addition to specifying the Identity Mechanism you will also have to manually specify which one of the identity members will be handled by the Identity Mechanism using the Identity Mechanism Member drop-down list.
Once an insert statement is executed for a domain class, its id column will be omitted from the generated insert statement and the value that has been generated by the backend will be obtained.
The NEWSEQUENTIALID() function is supported only for MSSQL 2005 (or later) and SQL Azure. You can specify the "DatabaseServerCalculated" identity mechanism for a Guid identity field only for MSSQL 2005 and above. For SQL 2000 and SQLServer Compact Edition a Guid identity field should use the "Guid" identity mechanism which will generate a unique Guid value on the client using the System.Guid.NewGuid() function.