SQL Server: 'Cannot insert explicit value for identity column when IDENTITY_INSERT is set to OFF' (error 544)

Cannot insert explicit value for identity column in table 'Customers' when IDENTITY_INSERT is set to OFF. (error 544) means your INSERT supplied a value for a column that SQL Server numbers itself. Leave the identity column out of the column list and let the server pick the number.

If you really need a specific value, for example when copying rows from another database, run SET IDENTITY_INSERT Customers ON before the insert in the same session and switch it off again afterwards.

The error

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=Fix4Lab;Integrated Security=true;TrustServerCertificate=true' 'INSERT INTO Customers (Id, Name, Email) VALUES (10, N'David Chen', N'david.chen@example.test')'
ex.Number = 544
Microsoft.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'Customers' when IDENTITY_INSERT is set to OFF.

Why it happens

The table was created with Id INT IDENTITY(1,1) PRIMARY KEY, which tells SQL Server to generate Id on every insert. While the IDENTITY_INSERT setting is off, which is the default, the server refuses any statement that tries to write that column, even when the value would be free. The usual sources are an INSERT written by copying all columns from a SELECT, a seed script exported from another database, and EF Core code that sets the key by hand on an entity whose key is an identity column.

The fix

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=Fix4Lab;Integrated Security=true;TrustServerCertificate=true' 'INSERT INTO Customers (Name, Email) VALUES (N'David Chen', N'david.chen@example.test'); SELECT Id, Name FROM Customers ORDER BY Id'
1 | Maria Garcia
2 | David Chen
OK (1 row(s) affected)

Without Id in the column list the insert succeeded and the server gave the new row the next number, 2. When the value itself matters, switch the setting on for that table, insert, and switch it off, all in the same batch or at least the same connection:

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=Fix4Lab;Integrated Security=true;TrustServerCertificate=true' 'SET IDENTITY_INSERT Customers ON; INSERT INTO Customers (Id, Name, Email) VALUES (10, N'Aiko Tanaka', N'aiko.tanaka@example.test'); SET IDENTITY_INSERT Customers OFF; SELECT Id, Name FROM Customers ORDER BY Id'
1 | Maria Garcia
2 | David Chen
10 | Aiko Tanaka
OK (1 row(s) affected)

IDENTITY_INSERT belongs to the session, so turning it on in one connection and inserting through another fails again. With IDENTITY_INSERT on, the column list must name the identity column, and only one table per session can have it on at a time. In EF Core, leave the key at its default value so the database generates it.

How it was reproduced

A console app from dotnet new console -n FixLab -f net10.0 (.NET SDK 10.0.401) with Microsoft.Data.SqlClient 7.1.1, which runs the SQL in its second argument and prints ex.Number and the message on failure. The test database Fix4Lab had one table, dbo.Customers, with an INT IDENTITY(1,1) key and one fictional row, on SQL Server 2022 LocalDB 16.0.1200.5 on Windows 11. An insert with Id = 10 failed with error 544; both fixes ran as shown.

Frequently asked

How do I fix Cannot insert explicit value for identity column?

Remove the identity column from the INSERT column list and let SQL Server generate it. If you must keep a specific value, run SET IDENTITY_INSERT TableName ON in the same session, insert, then set it OFF.

Why does SET IDENTITY_INSERT ON not work from my application?

The setting applies only to the session that set it. It must run on the same open connection as the INSERT, ideally in the same batch, and the INSERT must list the identity column explicitly.

How do I avoid error 544 in EF Core?

Do not assign the key yourself on an entity whose key column is an identity column; leave it at the default and EF Core lets the database generate it. Seed data with fixed keys goes through HasData, which handles this in migrations.

Choosing between identity, GUID and natural keys in the first place is covered in the primary keys part of Database Design for Developers. More decoded errors in the Fixes category.