UNIQUE KEY Violation error not showing duplicated data

Question

In SQL Server 2008, while inserting a set of records to my table table1 , i am getting a unique key violation error due to the Duplicate data. That is fine, but the error message is not showing which data is duplicated. But in SQL Server 2014 it is showing the duplicate key value also .

Error Message in 2014

Msg 2627, Level 14, State 1, Line 2 Violation of UNIQUE KEY constraint 'UK_table1'. Cannot insert duplicate key in object 'dbo.table1'. The duplicate key value is (xxx).

Error Message in 2008

Msg 2627, Level 14, State 1, Line 2 Violation of UNIQUE KEY constraint 'UK_table1'. Cannot insert duplicate key in object 'dbo.table1'.

Is this a new feature in 2014 or is there any way to show this in SQL Server 2008?

Note:- Hope the question is clear, if it is not please comment. Also if this is a new feature in 2008+, then please add valid reference/link for this

EDIT

2008

2014

See my edited post - it seems clear from the @@versions of yourself and @Ponmani that it was introduced in SQL 2008 R2. — strickt01
@strickt01, yes you are right, thank you. One more , is it possible to edit this sysmessages in 2008 to include this feature? Just to know? — Abdul Rasheed
Unfortunately not as the internal code calling RAISERROR won't be passing the requisite parameters to that message. You'll need to upgrade... — strickt01

strickt01 strickt01 · Accepted Answer · 2016-04-15T09:06:32

Run the following on both servers:

select * from sysmessages sm where sm.error = 2627

You should see that the message templates are different. Which exact version of SQL Server 2008 are you using because it was definitely available in 2008 R2:

Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'. The duplicate key value is %ls.

Looking back at SQL Server 2005 databases though the template is:

Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.

From looking at your @@version it is clear that it was not available in SQL Server 2008 RTM but that from @Ponmani's @@version that the feature was added by SQL Server 2008 R2.

UNIQUE KEY Violation error not showing duplicated data

2 Answers