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.