A myth around IDENTITY columns is that values cannot be explicitly inserted into IDENTITY columns. This myth may be coming from the fact that IDENTITY columns compute their values automatically when the INSERT operation is performed and that by default, attempting to insert explicit values into an IDENTITY column throws us an error with Msg #544:
USE tempdb;
GO
SET NOCOUNT ON;
--1. Prepare the environment
-- Create a table, and add some test data into it
--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
DROP TABLE dbo.tIdentity;
GO
--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY(1,1),
IdentityValue VARCHAR(10)
);
GO
--Add some test data
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('One'),
('Two'),
('Three');
GO
--2. Check the current identity value of the table using IDENT_CURRENT()
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueBeforeTest;
GO
--3. Confirm that explicitly inserting data into the IDENTITY column results
-- into an error
INSERT INTO dbo.tIdentity (IdentityId, IdentityValue)
VALUES (5, 'Five');
GO
/*
Msg 544, Level 16, State 1, Line 4
Cannot insert explicit value for identity column in table 'tIdentity' when IDENTITY_INSERT is set to OFF.
*/
Msg 544, Level 16, State 1, Line 4
Cannot insert explicit value for identity column in table ‘tIdentity’ when IDENTITY_INSERT is set to OFF.
Busting the Myth – Explicit values cannot be inserted into IDENTITY columns
Only one question is sufficient to challenge this myth – How should the system work when data is being moved from one table to another in case of replication or in case of a data migration?
The answer to this question comes as a simple SET option – SET IDENTITY_INSERT. This SET option allows a user to explicitly specify the values for the IDENTITY column. The script below provides a quick demonstration for usage of this option. This script:
- Sets the IDENTITY_INSERT property to ON
- Inserts two records with an explicit value for the IdentityId (IDENTITY column)
- Checks the new value of the identity property using the IDENT_CURRENT function
USE tempdb;
GO
SET NOCOUNT ON;
--Assumption: Query #06 has already been executed
SET IDENTITY_INSERT dbo.tIdentity ON;
GO
INSERT INTO dbo.tIdentity (IdentityId, IdentityValue)
VALUES (6, 'Six'),
(7, 'Seven');
GO
--6. Select the values from the table
SELECT ti.IdentityId, ti.IdentityValue
FROM dbo.tIdentity AS ti;
GO
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterTest;
GO
--Cleanup
SET IDENTITY_INSERT dbo.tIdentity OFF;
GO
IF OBJECT_ID('tIdentity','U') IS NOT NULL
DROP TABLE dbo.tIdentity;
GO
/**********************************************
RESULTS
**********************************************/
/*
IdentityId IdentityValue
----------- -------------
1 One
2 Two
3 Three
6 Six
7 Seven
IdentityValueAfterTest
----------------------
7
*/
Please NOTE however that one needs to turn OFF IDENTIYTY_INSERT for normal operation of IDENTITY columns to be resumed.
As can be seen from the script above, explicitly inserting values into an Identity column causes the system to increment the internal identity counter also.
Conclusion
The test shown above clearly proves that it is possible to explicitly insert values into an IDENTITY column. This option (IDENTITY_INSERT) is very useful when working on data migration packages where it allows to pump data from one table to another “as-is”.
Until we meet next time,
SQLTwins by Nakul Vachhrajani 