SQL Server Error 544

Error number

544

Severity

16 (user-correctable error)

State

1

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Database, Azure SQL Managed Instance

Category

Syntax & Object Resolution

Last reviewed

23 October 2026

What it means

Error 544 means an INSERT named the identity column in its column list and gave it a value, but IDENTITY_INSERT is not ON for that table in the current session and scope.

An identity column normally receives its value from SQL Server. To write your own value into it, the session must first run SET IDENTITY_INSERT table ON. Without that, an INSERT whose column list includes the identity column fails with 544 at severity 16 and no row is inserted. The table in the message is shown without its schema, for example 'Customers'.

544 is the column-list form of the problem: you named the identity column and the only thing missing is the setting. If the INSERT has no column list at all, SQL Server raises error 8101 instead, because an explicit identity value needs both a column list and IDENTITY_INSERT ON. The setting is per session and per scope: ON set inside dynamic SQL or a stored procedure reverts when that scope ends, and only one table per session can have it ON at a time.

Common causes

• An INSERT that lists the identity column, for example to load reference data with fixed ids.
• Copying rows between tables or environments with INSERT ... SELECT and keeping the original ids.
• SET IDENTITY_INSERT ON run in a different scope (dynamic SQL, a stored procedure, another connection) from the INSERT.
• SET IDENTITY_INSERT ON run for a different table than the one being inserted into.
• Application or ORM code that sends a value for a key column it does not know is an identity column.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT ic.name AS identity_column, ic.seed_value, ic.increment_value, ic.last_value
FROM sys.identity_columns AS ic
WHERE ic.object_id = OBJECT_ID(N'dbo.Customers');

SELECT IDENT_CURRENT(N'dbo.Customers') AS current_identity,
       (SELECT MAX(id) FROM dbo.Customers) AS max_id;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

INSERT INTO dbo.Customers (name)
VALUES (N'Ana');

SELECT SCOPE_IDENTITY() AS new_id;

SET IDENTITY_INSERT dbo.Customers ON;

INSERT INTO dbo.Customers (id, name)
SELECT id, name FROM dbo.CustomersOld;

SET IDENTITY_INSERT dbo.Customers OFF;

EXEC (N'SET IDENTITY_INSERT dbo.Customers ON;
       INSERT INTO dbo.Customers (id, name) VALUES (10, N''Ana'');
       SET IDENTITY_INSERT dbo.Customers OFF;');

Examples

Seeding reference rows with fixed ids

CREATE TABLE dbo.Customers (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100));

INSERT INTO dbo.Customers (id, name) VALUES (10, N'Ana');

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'Customers' when IDENTITY_INSERT is set to OFF.

Copying rows with their original ids

CREATE TABLE dbo.Customers (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100));
CREATE TABLE dbo.CustomersOld (id INT, name NVARCHAR(100));
INSERT INTO dbo.CustomersOld VALUES (1, N'Ana');

INSERT INTO dbo.Customers (id, name)
SELECT id, name FROM dbo.CustomersOld;

Msg 544, Level 16, State 1, Line 5
Cannot insert explicit value for identity column in table 'Customers' when IDENTITY_INSERT is set to OFF.

IDENTITY_INSERT set ON inside dynamic SQL, then lost

CREATE TABLE dbo.Customers (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100));

EXEC (N'SET IDENTITY_INSERT dbo.Customers ON;');  -- reverts when the dynamic batch ends
INSERT INTO dbo.Customers (id, name) VALUES (10, N'Ana');

Msg 544, Level 16, State 1, Line 4
Cannot insert explicit value for identity column in table 'Customers' when IDENTITY_INSERT is set to OFF.

How to prevent it

Reserve explicit identity values for migrations and fixed reference data, and wrap each such load in SET IDENTITY_INSERT ON and OFF in the same batch. Application code should leave the identity column out of its INSERTs and read the new value back with SCOPE_IDENTITY() or an OUTPUT clause.

If rows need stable, known keys across environments, consider a plain key column (or a SEQUENCE) instead of IDENTITY. For background on keys, see primary key vs foreign key.

Common questions

Learn more on CodeWithSQL

• DML commands lesson
• SQL constraints explained
• Database migration checklist

Official documentation

• SET IDENTITY_INSERT (Transact-SQL)
• IDENTITY (Property) (Transact-SQL)
• sys.identity_columns (Transact-SQL)

Last technically reviewed 23 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.