A identity is an MS SQL Server term used to indicate that unique values are automatically generated for each and every row of data that is inserted into a table. An identity value is not updated if columns of data are updated in a Transact-SQL statement. An identity value is automatically generated based on two parameters that you must specify, as follows:
- Identity Seed: The starting value used to generate identity values
- Identity Increment: The incremental value, which is added to the identity value.
Identity values are particularly helpful for use in a primary key because the value is guaranteed to be unique within the table, known as entity integrity.
Entity integrity and primary keys are discussed in a prior module.
Because arithmetic is performed by SQL Server to derive the next available identity value, you can only use the identity flag with the following datatypes:
- Int
- Smallint
- Tinyint
- Decimal (as long as the scale is 0)
- Numeric (as long as the scale is 0)
Only one column in a table can be flagged as an identity column.
In the next lesson, you will learn about the ROWGUIDCOL attribute flag for your columns.