Creating and Modifying Identifier Columns

Only one identifier column and one globally unique identifier column can be created per 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™ 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:

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:


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

         

To create a new identifier column on an existing table

         

To delete an identifier column

         

See Also
Auto-Numbering and Identifier Columns OBJECTPROPERTY
COLUMNPROPERTY uniqueidentifier
NEWID Using Uniqueidentifier Data

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.