IDENTITY columns are managed by SQL Server. Most of the times you would not dare to touch the values, except for reading them. However, there may be times when you really need to update/insert explicit values, instead of letting SQL Server generate a value for you.
How do I insert an explicit value into an IDENTITY column?
By default SQL Server does not allow you to insert a value into an IDENTITY column. You need to explicitly enable IDENTITY INSERT. Let us see some sample code to understand this. Run the following script to create the sample table and data.
IF OBJECT_ID('Employees','U') IS NOT NULL
DROP TABLE Employees
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO
INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'
DELETE FROM Employees WHERE Name = 'Steve'
INSERT INTO Employees (Name) SELECT 'Bob'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
3 Bob
*/
Note that we inserted two records, then deleted the record of 'Steve' and then inserted 'Bob'. This indeed created a missing sequence number. The next insert will generate IDENTITY value 4. Let us try to insert a record for Steve with EmpID 2.
SET IDENTITY_INSERT Employees ON
INSERT INTO Employees (EmpID, Name) SELECT 2, 'Steve'
SET IDENTITY_INSERT Employees OFF
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
2 Steve
3 Bob
*/
SET IDENTITY_INSERT ON enables IDENTITY INSERT on the specified table. You should TURN it off as soon as you are done with inserting the value. Only one TABLE per session can have IDENTITY INSERT set to ON at a time. If you try to enable IDENTITY INSERT on another table, SQL Server will generate an error.
SET IDENTITY_INSERT Employees ON
CREATE TABLE Departments (DeptID INT IDENTITY)
GO
SET IDENTITY_INSERT Departments ON
/*
Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'master.dbo.Employees'.
Cannot perform SET operation for table 'Departments'.
*/
To enable IDENTITY INSERT on another table, you should set it to OFF on the previous table. The following is valid.
SET IDENTITY_INSERT Employees ON
INSERT INTO Employees (EmpID, Name) SELECT 5, 'Jacob'
SET IDENTITY_INSERT Employees OFF
-- enable IDENTITY INSERT on Departments Table
SET IDENTITY_INSERT Departments ON
When IDENTITY INSERT is set to ON, you should always specify an IDENTITY value, else the insert operation will fail.
SET IDENTITY_INSERT Employees ON
INSERT INTO Employees (Name) SELECT 'Steve'
/*
Msg 545, Level 16, State 1, Line 2
Explicit value must be specified for identity column in table
'Employees' either when IDENTITY_INSERT is set to ON or when a
replication user is inserting into a NOT FOR REPLICATION
identity column.
*/
When you explicitly insert a new IDENTITY value, if that is higher than the current identity value, SQL Server will automatically adjust the current IDENTITY value to the new value.
TRUNCATE TABLE Employees
INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'
-- current IDENTITY VALUE is 2
SET IDENTITY_INSERT Employees ON
INSERT INTO Employees (EmpID, Name) SELECT 100, 'Jacob'
SET IDENTITY_INSERT Employees OFF
-- current IDENTITY VALUE IS 100
INSERT INTO Employees (Name) SELECT 'Jacob'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
2 Steve
100 Jacob
101 Jacob
*/
How do I UPDATE the value of an identity column?
You cannot update an IDENTITY column. SQL Server will generate an error if you attempt to do that. The following example demonstrates that.
SET IDENTITY_INSERT Employees ON
UPDATE Employees SET
EmpID = EmpID + 100
WHERE Name = 'Jacob'
/*
Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'EmpID'.
*/
Question to Readers: How often do you insert explicit values to an IDENTITY column? Did you ever come across cases where you needed to UPDATE an IDENTITY value?