Creating and Modifying Identifier Columns

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 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.

  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 (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

CREATE TABLE (Transact-SQL)

To create a new identifier column on an existing table

ALTER TABLE (Transact-SQL)

To delete an identifier column

ALTER TABLE (Transact-SQL)

How to: Delete Columns from a Table (Visual Database Tools)

To obtain information about identity columns

sys.identity_columns (Transact-SQL)

See Also

Concepts

Autonumbering and Identifier Columns
Using uniqueidentifier Data

Other Resources

COLUMNPROPERTY (Transact-SQL)
NEWID (Transact-SQL)
OBJECTPROPERTY (Transact-SQL)
uniqueidentifier (Transact-SQL)
Replicating Identity Columns

Help and Information

Getting SQL Server 2005 Assistance

Change History