Using automatically incrementing IDENTITY columns is
very popular with database developers. You don’t need to explicitly
calculate unique surrogate keys when inserting new data; the IDENTITY
column functionality does that for you. The IDENTITY feature also allows
you to specify useful Seed and Increment properties. When you use an INSERT
statement to insert data into a table with an IDENTITY column defined,
SQL Server will generate a new IDENTITY value. You can use the @@IDENTITY variable and the SCOPE_IDENTITY and IDENT_CURRENT functions to return the last IDENTITY
value that has been generated by SQL Server. This is very useful when
you need to return the key for the row that has just been inserted, back
to the caller. In Example 1
a stored procedure inserts a new row into the Person.Contact table, and
returns the IDENTITY value of the new record. This value can then be
used in other statements.
Example 1. Using @@IDENTITY to Return the Last IDENTITY Value Generated
USE AdventureWorks;
GO
CREATE PROCEDURE sp_InsertPerson
@FirstName nvarchar(50), @LastName nvarchar(50), @PersonKey int OUT
AS
INSERT Person.Contact(FirstName, LastName, PasswordHash, PasswordSalt)
VALUES (@FirstName, @LastName, 'NO PASSWORD', 'NONE')
SET @PersonKey = @@IDENTITY
GO
DECLARE @NewKey int;
EXECUTE sp_InsertPerson 'Valentine', 'Boiarkine', @NewKey OUT
SELECT @NewKey
GO
-- Results:
-- (1 row(s) affected)
-- -----------
-- 19990
|
We could have used SCOPE_IDENTITY and IDENT_CURRENT in this stored procedure with the same results. The difference between these functions is scope. The @@IDENTITY variable holds the last IDENTITY value generated by SQL Server for the current session. The SCOPE_IDENTITY
function returns the last IDENTITY value generated by SQL Server
limited to the current session and scope only. This is useful when you
are inserting into a table with a trigger bound to it, and the trigger
itself causes the generation of another IDENTITY value. The IDENT_CURRENT
function is not restricted by session or scope. This function returns
the value of the last IDENTITY generated for a specific table. IDENT_CURRENT requires a table name parameter.
Example 2 shows the correct use of the SCOPE_IDENTITY and IDENT_CURRENT functions.
Example 2. Using @@IDENTITY, IDENT_CURRENT, and SCOPE_IDENTITY
USE AdventureWorks;
GO
INSERT Production.ScrapReason(Name)
VALUES ('Glue too weak')
SELECT @@IDENTITY as IDENTITY_VALUE,
IDENT_CURRENT('Production.ScrapReason') as IDENT_CURRENT_VALUE,
SCOPE_IDENTITY() as SCOPE_IDENTITY_VALUE
GO
-- Results:
-- (1 row(s) affected)
-- IDENTITY_VALUE IDENT_CURRENT_VALUE SCOPE_IDENTITY_VALUE
----------------- ------------------- --------------------
-- 23 23 23
-- (1 row(s) affected)
|
How do you decide when to use SCOPE_IDENTITY versus @@IDENTITY versus IDENT_CURRENT?
When your DML statement has affected only one table, and you are
querying the IDENTITY value from the same connection that made the
modifications, all three will return the same value. This is the case in
the previous example. However, if you query @@IDENTITY and SCOPE_IDENTITY from a new connection, they will return NULL. This is because @@IDENTITY and SCOPE_IDENTITY values are specific to the connection that executed the DML statement that changed the IDENTITY values. When would @@IDENTITY and IDENT_CURRENT functions return different values? The @@IDENTITY function returns the last IDENTITY value created in any scope within the connection, whereas IDENT_CURRENT
is specific to the current scope. These functions will return different
values if an IDENTITY value for a second table is changed in a nested
scope—for example, by a trigger.
In Example 3,
we will work with two tables: Fruit and FruitAudit; both containing
IDENTITY columns named FruitID. The Fruit table contains three rows, and
FruitAudit contains five. A trigger is defined on the Fruit table that
will insert the new value into the FruitAudit table. When we insert a
new row into Fruit, two IDENTITY values will be produced: one for the Fruit table and one for the FruitAudit table, which was modified by the trigger. The @@IDENTITY
will return the last IDENTITY value regardless of scope (i.e., the
IDENTITY value for the FruitAudit table). SCOPE_IDENTITY will return the
IDENTITY value from the current scope, not
the nested scope of the trigger. SCOPE_IDENTITY will return the
IDENTITY value for the Fruit table. Try going through the following
example yourself—it will give you a closer understanding of the various
IDENTITY functions.
Example 3. Demonstrating the Use of Different IDENTITY Functions
CREATE TABLE dbo.Fruit (FruitID int IDENTITY PRIMARY KEY, FruitName
varchar(50))
GO
CREATE TABLE dbo.FruitAudit (FruitID int IDENTITY PRIMARY KEY, FruitName
varchar(50))
GO
CREATE TRIGGER Fruit_Inserted ON dbo.Fruit AFTER INSERT
AS
INSERT FruitAudit(FruitName) SELECT FruitName FROM INSERTED
GO
INSERT FruitAudit(FruitName) Values ('Mango'), ('Watermelon')
GO
INSERT Fruit(FruitName) Values ('Apple'), ('Banana'), ('Apricot')
GO
INSERT Fruit Values ('Pear')
- Execute the following statement from the same connection
SELECT @@IDENTITY as [@@IDENTITY], SCOPE_IDENTITY() as [SCOPE_IDENTITY],
IDENT_CURRENT('Fruit') as [IDENT_CURRENT_FRUIT],
IDENT_CURRENT('FruitAudit') as [IDENT_CURRENT_FRUITAUDIT]
-- Results (same connection):
-- @@IDENTITY SCOPE_IDENTITY IDENT_CURRENT_FRUIT IDENT_CURRENT_FRUITAUDIT
-- ---------- -------------- ------------------- ------------------------
-- 6 4 4 6
-Execute the following statement from a new connection
SELECT @@IDENTITY as [@@IDENTITY], SCOPE_IDENTITY() as [SCOPE_IDENTITY],
IDENT_CURRENT('Fruit') as [IDENT_CURRENT_FRUIT],
IDENT_CURRENT('FruitAudit') as [IDENT_CURRENT_FRUITAUDIT]
-- Results (different connection):
-- @@IDENTITY SCOPE_IDENTITY IDENT_CURRENT_FRUIT IDENT_CURRENT_FRUITAUDIT
-- ---------- -------------- ------------------- ------------------------
-- NULL NULL 4 6
|
Warning
Remember that @@IDENTITY is specific to the local server. When executing the INSERT statement against a remote table on a linked server, the last IDENTITY value generated for that table will not be held in @@IDENTITY. To obtain the remote @@IDENTITY value, execute a stored procedure in the context of the remote server, assigning the @@IDENTITY value to an output parameter.
Using IDENTITY_INSERT to Insert Explicit Values into IDENTITY Columns
You may wish to override the auto-generating behavior of IDENTITY columns; for example, if you have a table with an IDENTITY column defined, but you have deleted many rows in this table. The IDENTITY
value will never decrement itself, and therefore you will experience
gaps in your key values. If this behavior is undesirable, you can use
the SET IDENTITY_INSERT [database_name]. [schema_name].table_name ON | OFF statement to enable IDENTITY_INSERT functionality. Example 4 demonstrates the proper use of IDENTITY_INSERT.
Example 4. Using IDENTITY_INSERT
CREATE TABLE Fruit (FruitID int IDENTITY PRIMARY KEY, FruitName nvarchar(50))
GO
INSERT Fruit (FruitName) VALUES ('Apple'), ('Pomegranate'), ('Kiwifruit')
SELECT * FROM Fruit
GO
-- Results:
-- FruitID FruitName
-- ----------- -------------
-- 1 Apple
-- 2 Pomegranate
-- 3 Kiwifruit
DELETE Fruit WHERE FruitID = 1 OR FruitID = 3
INSERT Fruit (FruitName) VALUES ('Jackfruit'), ('Mango')
SELECT * FROM Fruit
GO
-- Results (Note that we now have gaps in key values):
-- FruitID FruitName
-- ----------- -----------
-- 2 Pomegranate
-- 4 Jackfruit
-- 5 Mango
INSERT Fruit (FruitID, FruitName) VALUES (1, 'Seaberry'), (3, 'Durian')
GO
-- Results:
-- Msg 544, Level 16, State 1, Line 1
-- Cannot insert explicit value for identity column in table 'Fruit' when
IDENTITY_INSERT is set to OFF.
SET IDENTITY_INSERT Fruit ON
INSERT Fruit (FruitID, FruitName) VALUES (1, 'Seaberry'), (3, 'Durian')
SET IDENTITY_INSERT Fruit OFF
SELECT * FROM Fruit
GO
-- Results:
-- FruitID FruitName
-- ----------- -----------
-- 1 Seaberry
-- 2 Pomegranate
-- 3 Durian
-- 4 Jackfruit
-- 5 Mango
|
Tip
Remember that the IDENTITY value for a table is incremented even if an INSERT
statement fails. This means failed DML statements and transactions will
cause gaps in the IDENTITY column. You can correct the gaps by setting IDENTITY_INSERT to ON, and manually inserting new data with explicitly specified IDENTITY values.
|
SQL
Server automatically maintains IDENTITY values for tables with IDENTITY
columns defined. However, you may wish to reset the IDENTITY value back
to its initial value, or to a specified value. You may also wish to
view the current IDENTITY value for a table. These tasks are performed
using the DBCC CHECKIDENT statement. The statement is used as follows:
DBCC CHECKIDENT (table_name, NORESEED | RESEED, [new_reseed_value])
When NORESEED
is specified, the IDENTITY value for the table is not reset. The
current IDENTITY value and the maximum value contained in the IDENTITY
column are returned. If the two values are not the same, you may wish to
reset the IDENTITY value to avoid gaps in IDENTITY column values.
When RESEED
is specified, the identity value for the table is reset to either the
maximum value contained in the IDENTITY column, or a value you specified
for new_reseed_value. If the table contains no data, the identity value for the table is reset to its initial value.
The DBCC CHECKIDENT statement is especially useful after you have used IDENTITY_INSERT. For example, you may have just inserted a number of explicit IDENTITY values into a table. You can now use IDENTITY_INSERT to reset the IDENTITY value to the next logical value, so that the next record receives the correct IDENTITY value.
You may want to use the DBCC CHECKIDENT
statement when you have emptied a table. Although the table is empty,
the IDENTITY value remains at what it was when the table had data. You
can now use the DBCC CHECKIDENT statement to reset the IDENTITY back to its initial value (usually 1).
|
Using the NEWID Function
Columns of type uniqueidentifier store globally unique identifier (GUID) data. Sometimes you need to insert values into these columns. The NEWID function can be used to generate a new value of type uniqueidentifier. This value is guaranteed to be globally unique. Example 5 demonstrates the use of the NEWID function.
Example 5. Using the NEWID Function
CREATE TABLE SpareParts (PartID uniqueidentifier PRIMARY KEY,
PartName nvarchar(50))
GO
INSERT SpareParts(PartID, PartName)
VALUES
(NEWID(), 'Fibre Channel Card'),
(NEWID(), 'Diagnostics Card')
SELECT * FROM SpareParts
-- Results:
-- PartID PartName
-- ------------------------------------ -------------------
-- AD4A4B10-A62B-4322-905A-9A5C51EB6010 Fibre Channel Card
-- 8750D92A-9FAC-4DC2-AB92-FB7A9C76E333 Diagnostics Card
|
Warning
Do not confuse the @@IDENTITY and NEWID functions. The @@IDENTITY
function holds the value of the last IDENTITY value generated by the
server for the current user session. The NEWID() function generates and
returns a new GUID.
Exercise. Working With Identity and Uniqueidentifier Columns
In this exercise, we will practice working with IDENTITY and UNIQUEIDENTIFIER data.
Before you begin, you must have the following software installed on your computer:
We will be adding rows to the Purchasing.ShipMethod table in the AdventureWorks database.
Open SQL Server Management Studio. Click Start | All Programs | Microsoft SQL Server 2008 | SQL Server Management Studio. Create a new query against the AdventureWorks database. Create a table named Stars by executing the following statement: CREATE TABLE Stars
(StarID uniqueidentifier PRIMARY KEY,
StarName nvarchar(50)) Use the NEWID
function to insert two rows into the Stars table with star names
‘Deneb’ and ‘Pollux’. Write and execute your statement as follows: INSERT Stars (StarID, StarName)
VALUES (NEWID(), 'Deneb'),
(NEWID(), 'Pollux') Use the SELECT statement to view the data contained in a table. Yours statement and results should look as follows. SELECT * FROM Stars
-- Results:
-- StarID StarName
-- ------------------------------------ ---------
-- 57559159-5B56-4346-9871-0ED3A228D6C1 Pollux
-- 1861C6D2-AB79-431B-A5D9-8E42FDE1F4B5 Deneb Create a new query against the AdventureWorks database. Create a table named Planets by executing the following statement: CREATE TABLE Planets
(PlanetID int IDENTITY PRIMARY KEY,
PlanetName nvarchar(50)) Use the INSERT
statement with PlanetName only in the ColumnList to insert three rows
into the Planets table: ‘Mars’, ‘Earth’, and ‘Jupiter’. Examine the data
contained in the table, and the value of the @@IDENTITY variable. Write and execute your statement as follows: INSERT Planets (PlanetName)
VALUES('Mars'), ('Earth'), ('Jupiter')
SELECT * FROM Planets
SELECT @@IDENTITY as [Last IDENTITY]
-- Results:
-- PlanetID PlanetName
-- ----------- -----------
-- 1 Mars
-- 2 Earth
-- 3 Jupiter
-- Last IDENTITY Delete the record with PlanetID = 2. Examine the data contained in the Planets table. Write and execute your statement as follows: DELETE Planets WHERE PlanetID = 2
SELECT * FROM Planets
-- Results:
-- PlanetID PlanetName
-- ----------- -----------
-- 1 Mars
-- 3 Jupiter Using IDENTITY_INSERT, insert the planet Venus with a PlanetID of 2. Write and execute your statement as follows: SET IDENTITY_INSERT Planets ON
INSERT Planets (PlanetID, PlanetName)
VALUES(2, 'Venus')
SET IDENTITY_INSERT Planets OFF
SELECT * FROM Planets
-- Results:
-- PlanetID PlanetName
-- ----------- -----------
-- 1 Mars
-- 2 Venus
-- 3 Jupiter
|
