Behaviour change for delete … output … into in SQL Server 2014

A colleague of mine discovered an interesting behaviour change from SQL Server 2012 to SQL Server 2014. We recently upgraded one of our servers from SQL 2012 to SQL 2014 and a job started failing. (Although I haven’t explicitly tested this behaviour on earlier versions – the job dates back to SQL 2005).

The error:

Msg 544, Level 16, State 1, Line 31
Cannot insert explicit value for identity column in table ‘#temp’ when IDENTITY_INSERT is set to OFF.

Fair enough – but the job, and procedure had not changed across the upgrade so why were we suddenly getting this error?

To test and try and isolate the issue I ran the following on a SQL 2012 SP2 and then a SQL 2014 RTM instance.

Create a table with an identity column:

use tempdb
go

create table ident_table (id int identity(1,1),val varchar(255))

insert into ident_table(val)
values ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h')

Create a copy of this table using the select into syntax:

select top 0 * 
into #temp
from ident_table

Verify that the identity property has copied across:

select * from sys.columns
where [object_id] = object_id('ident_table')

select * from sys.columns
where [object_id] = object_id('#temp')

Now we try and populate our temp table from our original table with an explicit insert:

insert 
into #temp(id,val)
select id,val from ident_table

Which fails as expected:

Msg 544, Level 16, State 1, Line 31
Cannot insert explicit value for identity column in table ‘#temp’ when IDENTITY_INSERT is set to OFF.

Populate the table using delete … output … into … :

delete ident_table
output deleted.id, deleted.val
into #temp(id,val)

On SQL 2012 this succeeds, on 2014 it fails with the error above!

I’ve tried a few different permutations of this test, including using a user database instead of tempdb and combining tables in a user database with tables in tempdb, or temp tables. In all cases the delete … output … into syntax seems to ignore the identity column in 2012 but throws an error in 2014.

There are a number of simple options for avoiding this issue – and I actually think that the 2014 behaviour makes more sense – but this still has me searching through our database code for other uses of the syntax.

I’m trying to encourage my colleague to create a Connect item for this – if/when he does – I’ll post the link to it here.

Advertisements
This entry was posted in SQL Server, T-SQL and tagged , , , . Bookmark the permalink.

4 Responses to Behaviour change for delete … output … into in SQL Server 2014

  1. Joe Celko says:

    Yet another reason not use IDENTITY and other proprietary physical locators in place of a real keys.

    • spaghettidba says:

      The error would still raise if the surrogate key was used along with a natural key, so your point is quite irrelevant. If you’re trying to say that surrogate keys shouldn’t be used at all, it would be pretending that established practices don’t exist.

  2. John Cooper says:

    I could not agree more Joe.

  3. Pingback: (SFTW) SQL Server Links 05/06/15 - John Sansom

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s