SQLConfiguration SQLConfiguration

Table Column Attributes  «Prev  Next»
Lesson 6 Using the identity attribute flag
Objective Indicate which columns in your table are identity columns.

Using the identity attribute flag in SQL-Server

A identity is an MS SQL Server 2000 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:
  1. Identity Seed: The starting value used to generate identity values
  2. 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.

When to use identity flags

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:
  1. Int
  2. Smallint
  3. Tinyint
  4. Decimal (as long as the scale is 0)
  5. 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.