What is an identity? What is the value? How can you capture the last identity value per column?

Answer 1

*    An identity is a property of a column where an seed and increment are defined.  The seed is the value that the column starts with and the increment is the value by which the identity grows.  In many circumstances the seed and increment values are 1 which means that the initial value is 1 and the identity grows by 1.
*    The value of the identity column is that the relational engine manages the values so you do not have to write logic to manage the identity values in multiple locations in the programming logic.
*    SQL Server provides three different functions for capturing the last generated identity value on a table that contains an identity column:
*        @@IDENTITY
*        SCOPE_IDENTITY()
*        IDENT_CURRENT(‘tablename’)

Answer 2

SQL Server IDENTITY columns are numeric columns which are set to auto increment when new values are inserted into the table. These columns are great for uniquely identifying records in a table, especially when you do not have any natural key to define a primary key constraint on the table.  Like a primary key, there can only be one IDENTITY property defined on a column in a table.

An IDENTITY column has a seed value (where the values begin) and the numeric increment value for each new record inserted. When new values are inserted into the table, the new valued will be based on the increment value of the identity column. The first value inserted into the table will have the value of seed value as specified.

The following data types can have the IDENTITY property set for them: decimal, int, numeric, smallint, bigint, and tinyint.

Answer 3

SQL Server provides three different functions for capturing the last generated identity value on a table that contains an identity column:

*  @@IDENTITY
*  SCOPE_IDENTITY()
*  IDENT_CURRENT(‘tablename’)

All three functions return the last value inserted into an identity column by the database engine. However, the three differ in functionality depending on the scope (or source) of the insert (i.e. a stored procedure or a trigger) and the connection that inserted the row.

Function @@IDENTITY
-------------------------------
returns the last generated table identity value for the current connection across all scope (i.e. any called stored procedures and any fired triggers). This function is not table specific. The value returned will be for the last table insert where an identity value was generated.

Function SCOPE_IDENTITY()
-------------------------------
is identical to @@IDENTITY with the following very notable exception: the value returned is limited to the current scope (i.e. the executed stored procedure).

Function IDENT_CURRENT
-------------------------------
spans all scope and all connections to retrieve the last generated table identity value. Unlike @@IDENTITY and SCOPE_IDENTITY(), it is table specific and takes a tablename as a parameter.