Creating and Modifying Identifier Columns
You can use an identifier column to create an automatically incrementing identification number in a table. You can create only one identifier column and one GUID column for each table.
IDENTITY Property
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 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 in the order that those rows were originally inserted. 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.
Note
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 or NEWSEQUENTIALID() function. In addition, you can apply the ROWGUIDCOL property to indicate that the new column is a row GUID column. Unlike columns defined with the IDENTITY property, the Database Engine does not automatically generate values for a column of type uniqueidentifier. 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. For more information, see Using uniqueidentifier Data.
A column with the ROWGUICOL property can be referenced in a select list by using the $ROWGUID keyword. 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
How to: Delete Columns from a Table (Visual Database Tools)
To obtain information about identity columns
sys.identity_columns (Transact-SQL)
To check and correct the current identity value for a specified table
DBCC CHECKIDENT (Transact-SQL)
To set a new seed value