On this page
Why it happens
Error 2601 means you tried to write a row whose key value already exists in a column covered by a unique index. The index exists to reject duplicates, so SQL Server stops the statement and rolls it back rather than store two rows with the same key value.
The message names the index, the table, and the exact duplicate value: The duplicate key value is (sara@example.com). That value is your fastest clue: it tells you which row already exists. The error is severity 14, which is a normal, catchable constraint violation, not a server problem.
This is the same duplicate-key problem as error 2627, but the uniqueness is enforced by a UNIQUE INDEX rather than a PRIMARY KEY or UNIQUE constraint. SQL Server implements PRIMARY KEY and UNIQUE constraints with a unique index behind the scenes, so the two errors are close cousins: 2627 fires when a named constraint is violated, and 2601 fires when a standalone unique index is violated. The distinction is only about how the rule was declared, not what went wrong.
Examples
Inserting a duplicate value into a unique index
CREATE TABLE dbo.Users (
id INT IDENTITY(1,1),
email NVARCHAR(256)
);
CREATE UNIQUE INDEX UX_Users_Email ON dbo.Users (email);
INSERT INTO dbo.Users (email) VALUES ('sara@example.com');
INSERT INTO dbo.Users (email) VALUES ('sara@example.com'); -- email already existsMsg 2601, Level 14, State 1, Line 10 Cannot insert duplicate key row in object 'dbo.Users' with unique index 'UX_Users_Email'. The duplicate key value is (sara@example.com).
An UPDATE that creates a duplicate
-- Two rows exist with different emails.
-- Changing one to match the other collides in the unique index.
UPDATE dbo.Users SET email = 'sara@example.com' WHERE id = 2;Msg 2601, Level 14, State 1, Line 3 Cannot insert duplicate key row in object 'dbo.Users' with unique index 'UX_Users_Email'. The duplicate key value is (sara@example.com).
Duplicates inside an INSERT ... SELECT
INSERT INTO dbo.Users (email)
SELECT email FROM staging.Users; -- staging has two rows with sara@example.comMsg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.Users' with unique index 'UX_Users_Email'. The duplicate key value is (sara@example.com).
How to fix it
Insert only if the value does not already exist
Guard the INSERT so it runs only when the key is free. This is the simplest fix for double-submit and re-run scenarios.
IF NOT EXISTS (SELECT 1 FROM dbo.Users WHERE email = 'sara@example.com')
INSERT INTO dbo.Users (email) VALUES ('sara@example.com');Use MERGE to insert or update (upsert)
If a matching value should update the existing row instead of failing, use MERGE so one statement handles both cases.
MERGE dbo.Users AS t
USING (SELECT 'sara@example.com' AS email) AS s
ON t.email = s.email
WHEN MATCHED THEN UPDATE SET t.email = s.email
WHEN NOT MATCHED THEN INSERT (email) VALUES (s.email);Let the database generate the key
When the unique column is a surrogate id supplied by hand, make it an IDENTITY (or use a SEQUENCE) and stop passing a value, so every row gets a fresh number that cannot collide.
CREATE TABLE dbo.Users (
id INT IDENTITY(1,1) PRIMARY KEY,
email NVARCHAR(256)
);
-- Do not pass id; SQL Server assigns it
INSERT INTO dbo.Users (email) VALUES ('sara@example.com');De-duplicate the source before loading
When bulk loading, remove duplicates in the source first. ROW_NUMBER() keeps one row per key value.
INSERT INTO dbo.Users (email)
SELECT email
FROM (
SELECT email,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY email) AS rn
FROM staging.Users
) d
WHERE rn = 1;Skip duplicates with IGNORE_DUP_KEY = ON
A unique index has an option that constraints do not: create it with IGNORE_DUP_KEY = ON so that a multi-row INSERT keeps the non-duplicate rows and skips the duplicate ones with a warning instead of failing the whole statement. Use this only when silently dropping duplicate rows is acceptable.
CREATE UNIQUE INDEX UX_Users_Email
ON dbo.Users (email)
WITH (IGNORE_DUP_KEY = ON);
-- Duplicate rows are skipped, not inserted; the rest succeed
INSERT INTO dbo.Users (email)
SELECT email FROM staging.Users;How to prevent it
Use an IDENTITY column or a SEQUENCE for surrogate keys so the database, not the application, guarantees uniqueness. This removes the most common cause of 2601 outright.
Make writes idempotent: use IF NOT EXISTS or MERGE for inserts that might run twice, and validate values in the application before submitting. De-duplicate any staging or import data before loading it. For how unique indexes and constraints relate, see the constraints guide and the closely related error 2627.
Common questions
How do I fix SQL Server error 2601?
Stop inserting a value that already exists in the unique index. Either guard the INSERT with IF NOT EXISTS, use MERGE to update instead of duplicating, let SQL Server generate the key with an IDENTITY column, or create the index with IGNORE_DUP_KEY = ON to skip duplicates. The message tells you the duplicate value to look for.
What is the difference between error 2601 and 2627?
They are almost the same problem. Error 2601 is a violation of a UNIQUE INDEX, while error 2627 is a violation of a PRIMARY KEY or UNIQUE constraint. Because SQL Server enforces those constraints with a unique index internally, both mean you tried to insert a duplicate into something that must be unique, and the fixes are identical.
Why does error 2601 happen on an UPDATE?
An UPDATE that changes an indexed column to a value another row already holds also creates a duplicate, so the same unique index rejects it just as it would reject a duplicate INSERT.