Identity in SQL Server

SQL Server 2000 has three functions that return IDENTITY information. The result of each of these three functions is dependent on three factors:

• The session scope (which connection produced the IDENTITY value?)


• The table scope (which table produced the IDENTITY value?)


• The statement scope (where is the statement that produced the IDENTITY value?)
  (SQL Statements that are contained in the same batch, stored procedure, or trigger are considered to be in the same scope. So, if I call an INSERT that fires a trigger, I have two different scopes: scope 1 is inside the batch that called the INSERT, and scope 2 is inside the trigger.)

SELECT @@IDENTITY
This is everyone's favorite function, unchanged from earlier versions of SQL Server. It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.

SELECT IDENT_CURRENT('tablename')
This new function returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.

SELECT SCOPE_IDENTITY()
This new function returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value