What is Retrieving an identity value

To return the most recently assigned IDENTITY value within the same session and scope, use the SCOPE_IDENTITY function; like this:

SQLCopy

SELECT SCOPE_IDENTITY();

The SCOPE_IDENTITY function returns the most recent identity value generated in the current scope for any table. If you need the latest identity value in a specific table, you can use the IDENT_CURRENT function, like this:

SQLCopy

SELECT IDENT_CURRENT('Sales.Promotion');

Overriding identity values

If you want to override the automatically generated value and assign a specific value to the IDENTITY column, you first need to enable identity inserts by using the SET IDENTITY INSERT table_name ON statement. With this option enabled, you can insert an explicit value for the identity column, just like any other column. When you’re finished, you can use the SET IDENTITY INSERT table_name OFF statement to resume using automatic identity values, using the last value you explicitly entered as a seed.

SQLCopy

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

As you’ve learned, the IDENTITY property is used to generate a sequence of values for a column within a table. However, the IDENTITY property isn’t suitable for coordinating values across multiple tables within a database. For example, suppose your organization differentiates between direct sales and sales to resellers, and wants to store data for these sales in separate tables. Both kinds of sale may need a unique invoice number, and you may want to avoid duplicating the same value for two different kinds of sale. One solution for this requirement is to maintain a pool of unique sequential values across both tables.