Two requests both check that the value is absent, and one insert still fails. Duplicate key errors are the database enforcing a rule under concurrency. Catch the expected errors deliberately, return a useful response, and keep every unrelated failure visible instead of hiding it behind a friendly message.
Know Which Object Raises Duplicate Key Errors
SQL Server error 2627 identifies a primary-key or unique-constraint violation. Error 2601 identifies a duplicate value rejected by a unique index outside that constraint form. Both protect uniqueness, but their messages describe different enforcing objects.
I check the error number before interpreting the message. Applications should not depend on parsing an English message to decide whether a uniqueness rule failed. ERROR_MESSAGE remains useful diagnostic context and includes the duplicate-value information SQL Server reports.
The demonstration creates two temporary tables with separate enforcement mechanisms. The values are synthetic and the duplicates are intentional. Run the examples in one connection. A real procedure needs its own transaction contract, especially when several changes must succeed together. The duplicate is doing you a favor by refusing to become tomorrow's reconciliation problem.
Handle duplicate key errors for the intended constraint without turning an unrelated failure into an apparent successful request.
Reproduce Both Duplicate Key Errors
The first table uses a primary key, while the second uses a unique index on Code. Each TRY block submits one known duplicate. The CATCH returns the actual number and message so you can inspect your server's output without relying on an invented error display.
These catches are demonstration instrumentation. A production catch should handle only the expected error numbers and rethrow everything else. A permission failure, conversion error, or missing object must not become a successful duplicate response merely because it happened during an insert.
I retain the enforcing object's definition with the test. That makes the distinction between 2627 and 2601 clear to someone reviewing the procedure later. Also test the nonduplicate path. A catch that recognizes duplicates is incomplete if the successful insertion returns an inconsistent result shape or leaves its caller unsure which row was accepted.
CREATE TABLE #ConstraintKeys(ID int NOT NULL PRIMARY KEY);
INSERT #ConstraintKeys VALUES(1);
BEGIN TRY
INSERT #ConstraintKeys VALUES(1);
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
CREATE TABLE #IndexKeys(ID int NOT NULL,Code varchar(20) NOT NULL);
CREATE UNIQUE INDEX IX_IndexKeys_Code ON #IndexKeys(Code);
INSERT #IndexKeys VALUES(1,'A');
BEGIN TRY
INSERT #IndexKeys VALUES(2,'A');
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;Return the Existing Row Only for the Intended Rule
The next block handles 2601 and 2627 explicitly, while rethrowing any other error. It returns a simple duplicate response and the row associated with the requested code. In a real table with several unique rules, confirm that the violated rule matches the lookup you perform.
A duplicate on another unique column is not proof that the requested code already exists. Inspect the schema and design separate handling when several rules produce the same error numbers. ERROR_MESSAGE is diagnostic evidence, but a robust business response should use known constraints and validated identifiers rather than brittle localized text parsing.
What should the caller do with an existing row? Returning it can support an idempotent operation, but it must not imply the requested new payload was accepted. Compare the existing data with the submitted values when equivalence matters. A friendly message should explain the actual outcome instead of turning a rejected insert into an apparent successful modification.
DECLARE @Code varchar(20)='A';
BEGIN TRY
INSERT #IndexKeys VALUES(3,@Code);
SELECT N'Inserted' AS ResultStatus;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() NOT IN(2601,2627) THROW;
SELECT N'That code already exists.' AS ResultMessage,ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS DiagnosticMessage;
SELECT ID,Code FROM #IndexKeys WHERE Code=@Code;
END CATCH;
Do Not Treat IF NOT EXISTS as a Locking Contract
Two concurrent sessions can both read that a value is absent before either inserts it. The unique object remains the final enforcement point. A preliminary IF NOT EXISTS check does not reserve the missing key under ordinary read-committed behavior.
If the business requires coordinated get-or-create behavior, design the transaction and locking strategy deliberately, with a supporting unique index and appropriate error handling. Serializable range protection or another supported coordination pattern has concurrency costs that need testing. Do not add broad table locks as an unexplained cure.
Keep the constraint or unique index even when the application checks first. The database rule protects all writers, including future code paths and administrative operations. Removing it to eliminate the error removes the guarantee. The correct response is to decide which duplicate outcomes are expected and handle those outcomes while preserving the underlying integrity rule.
Prepare Bulk Input Without Hiding Its Duplicates
A multirow insert containing a duplicate can fail the entire statement. Filter existing target values for a bulk load when that is the approved policy, and deduplicate the source itself separately. NOT EXISTS against the target does not remove duplicates among incoming source rows.
The example below gives source rows stable identifiers and chooses one source row per code. The chosen row is deterministic under the stated ordering. The subsequent NOT EXISTS excludes existing target codes. A concurrent writer can still race with that check, so the unique index and transaction strategy remain necessary.
Report rejected or skipped source rows when the load needs reconciliation. Quietly discarding duplicates can conceal conflicting payloads. The business must define whether the first row wins, duplicates are errors, or equivalent repeats are acceptable. Keep that policy visible rather than allowing ROW_NUMBER to become an accidental conflict-resolution rule simply because it makes the INSERT succeed.
WITH SourceRows AS
(
SELECT * FROM(VALUES(4,'B'),(5,'B'),(6,'C')) AS v(ID,Code)
),Ranked AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Code ORDER BY ID) AS ChoiceNumber FROM SourceRows
)
INSERT #IndexKeys(ID,Code)
SELECT s.ID,s.Code FROM Ranked AS s
WHERE s.ChoiceNumber=1 AND NOT EXISTS(SELECT 1 FROM #IndexKeys AS t WHERE t.Code=s.Code);Test Transactions and Caller Outcomes
Test duplicate constraint values, duplicate index values, successful inserts, and an unrelated error. Confirm the caller receives a clear outcome for each case. Include concurrent requests when the procedure is intended to support competing writers.
With SET XACT_ABORT ON or a surrounding transaction, inspect XACT_STATE and roll back according to the procedure's documented ownership contract. An expected duplicate does not mean the transaction is automatically safe to continue. Do not commit an uncommittable transaction or roll back a caller's unrelated work without the agreed behavior.
Handle duplicate key errors as known integrity outcomes while preserving unexpected failures. Keep the unique rule, avoid racing assumptions, and return the existing row only when that response fits the violated rule and submitted request. A useful CATCH explains what happened without weakening the database guarantee.
Related reading on this blog: How to Catch Errors While Inserting Values in Table and Stop Blaming the User: Let Constraints Catch Bad Data.
An existence check is not a uniqueness guarantee, it is a read that can race with another writer.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
