SQL Server is messing with my Identity – solved.

In my last post, I was looking into creating new temporary tables from a SELECT INTO syntax when I ran across an issue that I couldn’t explain.

I realised that a situation like that cannot be allowed to continue on a blog post about SQL Server so I documented what I was experiencing (probably pretty poorly I may add) and said that when I had an answer, I would let you know.

Well I have an answer now and I confess that I wasn’t the one to figure it out.

However, I will try as best I can to explain it, mainly for myself though so I can look back on it.

---

Summary of the problem:

We have a table with an identity value of 1,000. When we select a subset of that table into a new table the identity value of the new table decreases to match the highest identity value of that subset.

From initial investigations, there is no visible evidence to show how this is achieved, so how is this happening?

Code:

SELECT * INTO #temp FROM dbo.A WHERE x1 = 1;

SELECT * FROM #temp;

---

Red Herrings:

When running a SELECT INTO query, if you were to enable STATISTICS PROFILE beforehand,

SET STATISTICS PROFILE ON;
SELECT A_ID, x1 INTO #Test FROM dbo.A WHERE x1 = 1;

…you will see an Argument column with the following code:

DEFINE:([Expr1007]=setidentity([LocalTesting].[dbo].[A].[A_ID],(-7),(0),N’#test’))

This is the red herring that I was talking about.

This Argument column threw me but don’t let it confuse you too. There is no -7 arithematic going on here.

---

Solution:

There’s actually two phases to a SELECT INTO statement and it is the second one that we are interested in here.

As Paul White (t | b) says:

The second phase performs an insert into the table the first phase created. This insert is done withidentity_insert semantics, so the identity values from the source table end up in the destination, unchanged. The highest value actually inserted is set as the last value used. You can use IDENT_CURRENT or sys.indentity_columns to see it.

So there is no addition/subtraction going on here.

SQL Server is simply going:

> Are we done inserting? We are? Great, what was that last identity value? 998? Great, that’s your new identity value for this table!