Creating and Modifying Identifier Columns

Creating and Maintaining Databases

Creating and Modifying Identifier Columns

Only one identifier column and one globally unique identifier column can be created for each table.

IDENTITY Property

Identifier columns can be implemented using the IDENTITY property, which allows the application 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 inserting values into a table with an identifier column, Microsoft® SQL Server™ 2000 automatically generates the next identity value by adding the increment to the seed.

When you use the IDENTITY property to define an identifier column, consider that:

A table can have only one column defined with the IDENTITY property, and that column must be defined using the 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 null values and must not contain a DEFAULT definition or object.
The column can be referenced in a select list by using the IDENTITYCOL keyword after the IDENTITY property has been set.
The OBJECTPROPERTY function can be used to determine if a table has an IDENTITY column, and the COLUMNPROPERTY function can be used to determine the name of the IDENTITY column.

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 entire database, or every database on every networked computer in the world, use the ROWGUIDCOL property, the uniqueidentifier data type, and the NEWID function.

When you use the ROWGUIDCOL property to define a globally unique identifier column, consider that:

A table can have only one ROWGUIDCOL column, and that column must be defined using the uniqueidentifier data type.
SQL Server 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 function to generate a globally unique value.
The column can be referenced in a select list by using the ROWGUIDCOL keyword after the ROWGUIDCOL property is set. This is similar to the way an IDENTITY column can be referenced using the IDENTITYCOL keyword.
The OBJECTPROPERTY function can be used to determine if a table has a ROWGUIDCOL column, and the COLUMNPROPERTY function can be used to determine the name of the ROWGUIDCOL column.
Because the ROWGUIDCOL property does not enforce uniqueness, the UNIQUE constraint should be used to ensure that unique values are inserted into the ROWGUIDCOL column.

Note If an identifier column exists for a table with 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.

To create a new identifier column when creating a table

Transact-SQL