The IDENTITY property allows you to specify a counter of values for a specific column of a table. Columns with numeric data types, such as TINYINT, SMALLINT, INT, and BIGINT, can have this property. The Database Engine generates values for such columns sequentially, starting with an initial value. Therefore, you can use the IDENTITY property to let the system generate unique numeric values for the table column of your choice.
Each table can have only one column with the IDENTITY property. The table owner can specify the starting number and the increment value, as shown in Example 1.
EXAMPLE 1
The result could be:
The product table is created first in Example 1. This table has the product_no column with the IDENTITY property. The values of the product_no column are automatically generated by the system, beginning with 10000 and incrementing by 1 for every subsequent value: 10000, 10001, 10002, and so on.
Some system functions and variables are related to the IDENTITY property. Example 1 uses the $identity variable. As the result set of Example 1 shows, this variable automatically refers to the column with the IDENTITY property.
To find out the starting value and the increment of the column with the IDENTITY property, you can use the IDENT_SEED and IDENT_INCR functions, respectively, in the following way:
As you already know, the system automatically sets identity values. If you want to supply your own values for particular rows, you must set the IDENTITY_INSERT option to ON before the explicit value will be inserted:
NOTE
Because the IDENTITY_INSERT option can be used to specify any values for a column with the IDENTITY property, IDENTITY does not generally enforce uniqueness. Use the UNIQUE or PRIMARY KEY constraint for this task.
If you insert values after the IDENTITY_INSERT option is set to ON, the system presumes that the next value is the incremented value of the highest value that exists in the table at that moment.
Leave a Reply