Only one identifier column and one globally unique identifier column can be created per table.
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:
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
Auto-Numbering and Identifier Columns | OBJECTPROPERTY |
COLUMNPROPERTY | uniqueidentifier |
NEWID | Using Uniqueidentifier Data |