Creating and Modifying Identifier Columns
You can create only one identifier column and one GUID column for each table.
You can implement identifier columns by using the IDENTITY property. This enables the developer to specify both an identity number for the first row inserted into the table (Identity Seed property) and an increment (Identity Increment property) to be added to the seed to determine successive identity numbers. When values are inserted into a table that has an identifier column, the SQL Server 2005 Database Engine automatically generates the next identity value by adding the increment to the seed. When you add identifier columns to existing tables, the identity numbers are added to the existing rows of the table, with the seed and increment values applied beginning with the order in which those rows were originally added. Identity numbers are also generated for any new rows that are added. You cannot modify an existing table column to add the IDENTITY property.
When you use the IDENTITY property to define an identifier column, consider the following:
A table can have only one column defined with the IDENTITY property, and that column must be defined by using a decimal, int, numeric, smallint, bigint, or tinyint data type.
The seed and increment can be specified. The default value for both is 1.
The identifier column must not allow for null values and must not contain a DEFAULT definition or object.
The column can be referenced in a select list by using the $IDENTITY keyword after the IDENTITY property has been set. The column can also be referenced by name.
The OBJECTPROPERTY function can be used to determine whether a table has an IDENTITY column, and the COLUMNPROPERTY function can be used to determine the name of the IDENTITY column.
SET IDENTITY_INSERT can be used to disable the IDENTITY property of a column by enabling values to be explicitly inserted.
If an identifier column exists for a table that has frequent deletions, gaps can occur between identity values. Deleted identity values are not reused. To avoid such gaps, do not use the IDENTITY property. Instead, you can create a trigger that determines a new identifier value, based on existing values in the identifier column, as rows are inserted.
Globally Unique Identifiers
Although the IDENTITY property automates row numbering within one table, separate tables, each with its own identifier column, can generate the same values. This is because the IDENTITY property is guaranteed to be unique only for the table on which it is used. If an application must generate an identifier column that is unique across the database, or every database on every networked computer in the world, use the uniqueidentifier data type and the NEWID (Transact-SQL) or NEWSEQUENTIALID() function. The Database Engine does not automatically generate values for the column. To insert a globally unique value, create a DEFAULT definition on the column that uses the NEWID or NEWSEQUENTIALID function to generate a globally unique value.
The column can be referenced in a select list by using the $ROWGUID keyword after the ROWGUIDCOL property is set. This is similar to the way an IDENTITY column can be referenced by using the $IDENTITY keyword. A table can have only one ROWGUIDCOL column, and that column must be defined by using the uniqueidentifier data type.
The OBJECTPROPERTY (Transact-SQL) function can be used to determine whether a table has a ROWGUIDCOL column, and the COLUMNPROPERTY (Transact-SQL) function can be used to determine the name of the ROWGUIDCOL column.
The following example creates a table with a uniqueidentifier column as a primary key. The example uses the
NEWSEQUENTIALID() function in the
DEFAULT constraint to provide values for new rows. The ROWGUIDCOL property is applied to the uniqueidentifier column so that it can be referenced using the $ROWGUID keyword.
CREATE TABLE dbo.Globally_Unique_Data (guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, Employee_Name varchar(60) CONSTRAINT Guid_PK PRIMARY KEY (guid) );
To create a new identifier column when you create a table
To create a new identifier column on an existing table
To delete an identifier column
To obtain information about identity columns
Help and Information
14 April 2006
5 December 2005