The IDENTITY
is a potential property for at most 1 column in a table. It will cause keys to
be automatically produced for new rows added to the table. The values used are
of type NUMERIC with scale 0 (i.e. whole number values without decimal). Unlike
sequences, the IDENTITY will not cycle. By default, IDENTITY values start at 1
and increment by 1 for each new row.
You cannot
update an IDENTITY column value. Any IDENTITY data type column will normally
automatically get a value generated when a row is inserted into the table. But
if you turn on the SET IDENTITY INSERT ON option, you can override the value
placed into the IDENTITY column with a value of your choosing.
The SCOPE_IDENTITY() function will return the
last identity value produced in your session and scope. And @@IDENTITY gives
you the last identity value for your entire session. In case of situations such
as a ROLLBACK, there can be gets in the values of the IDENTITY columns.
