I came across an interesting little enhancement which I’d like to share with you. I’m sure you’re all familiar with the following error message:
Msg 2601, Level 14, State 1, Line 4
Cannot insert duplicate key row in object ‘dbo.#t’ with unique index ‘PK_Unique’.
That’s right, the “hey, you’re inserting junk, stop that right now!” message.
However, earlier this week I encountered a variation of that error in our ETL logs:
Msg 2601, Level 14, State 1, Line 3
Cannot insert duplicate key row in object ‘dbo.#t’ with unique index ‘PK_Unique’. The duplicate key value is (1, 2).
I’m sure I don’t need to explain how useful that extra sentence can be, right? So, then I started digging because obviously I want that error to always include the offending values.
Recently our database back-end servers have been replaced with new machines running SQL Server 2008 R2 SP1. And indeed, as of R2 SP1, this error message has gotten an upgrade!
If you want to check for yourself, the following code snippet can be used to generate the error:
create table #t (PK1 int, PK2 int);
create unique index PK_Unique on #t(PK1, PK2);
insert into #t values (1, 2),(1, 2);
Now, message 2601 is not the only one complaining about duplicate keys. Another example can be generated using the following query:
create table #t (PK1 int unique, PK2 int unique);
insert into #t values (1, 2),(1, 2);
Executing that snippet on SQL Server 2008 R2 SP1 results in this error:
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint ‘UQ__#t________C5776555123EB7A3′. Cannot insert duplicate key in object ‘dbo.#t’. The duplicate key value is (2).
Do you notice the difference? The message did indeed get an upgrade, but is not able to support a simultaneous violation on multiple columns, while msg 2601 can. Hopefully they can get that fixed by SQL Server 2012 RTM!
Out of curiosity I decided to get a closer look at other possible variations on this error and dug into the sys.messages table using this query:
select * from sys.messages
where text like ('%duplicate key%');
Results of that query:
So, there’s one more to investigate: 1505. To get that error generated is not that complicated. We actually already have all the statements, just need to use them in the right order, like so:
create table #t (PK1 int, PK2 int);
insert into #t values (1, 2),(1, 2);
create unique index PK_Unique on #t(PK1, PK2);
And what do we get?
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.#t__________________________________________________________________________________________________________________00000000001D’ and the index name ‘PK_Unique’. The duplicate key value is (1, 2).
Nice, message 1505 also supports multiple columns, that makes two out of three!
Ow, don’t forget to clean up (yes, I even do that for temporary tables).
drop table #t;
Additional Info
Some further digging around on the internet brought me to the following suggestion on Microsoft Connect: Duplicate Key Values by Anton Plotnikov. Looks like our enhanced statements are the result of that request.
There’s another suggestion as well: Tweak To Duplicate Key Message by Louis Davidson. Louis suggests to have all offending values added to the message as well, but in a different situation than when dealing with multiple unique columns. You can create one insert statement that inserts more than one record with offending values, and that’s the situation he’s referring to. If you feel that’s important, get over to Connect and cast vote! I do think that the number of values would need to get limited in that case, we might end up with really long error messages otherwise.
One last link, also by Louis Davidson. He also wrote a blog post about the error message when he found out about it in Denali.
Have fun!
Valentino.
