SQL Server Performance Forum – Threads Archive

Guaranteed consecutive identity values

If I do a INSERT INTO <table> SELECT … and <table> has an identity column, does SQL/Server 2000 and/or SQL/Server 2005 guarantee that the identity values generated for a single INSERT of this type will be consecutive. That is, is it guaranteed that simultaneous INSERTs on other connections will not interrupt the sequence of identity values assigned. Thanks, Mike

-Mike,
in this case when you fire insert table select cols from table runs as a whole batch.
so only after completing this batch SQL will start another interuppted insert from another thread so which is gonna have identity may be before the first batch value or if triggered after the first batch then max value.
So here i feel batch statements can not interrupt each other.

SQL Server will take of this. Each row will get a unique value. You can even more enforce this, making such a column the PRIMARY KEY or put a unique index/constraint on that column. There is no guarantee that there will never be any gaps in your sequence. Due to data modifications, failed transactions or whatsoever it is very likely that you will have such gaps over time. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Thanks for those replies, but they were not directly addressing my question. I understand that gaps can occur as a result of deletes, rollbacks, etc. What I was asking was whether a single successful INSERT INTO … SELECT that inserts multiple rows will always have consecutive identity values assigned. Thanks, Mike

If you are doing multiple inserts, then I suppose the numbering may get ‘interrupted’ by other processes. In that case, you could put the multiple inserts into a single transaction. Of course the transaction should not take too long, otherwise yo might end up interrupting other processes. By the way, if there is a gap in the identity values, then a later insert with the SET IDENTITY_INSERT option (see BOL) could fill up that gap. So you should not put any intrinsic value on an identity value. Identity is only a surrogate key, and has no informational value at all.

I don’t think it will get interrupted. Only one process can get an exclusive lock to INSERT or otherwise modify data in a table. The other processes will have to wait. So, as only one process is doing INSERTs, that IDENTITY values should be consecutive. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Well. If the table has thousands of rows with datetime column, does adding Identity column to that table gaurantee that identity values are in ascending order of the dates? Madhivanan Failing to plan is Planning to fail

Depends on how you add the IDENTITY column. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] When you do an INSERT INTO table SELECT FROM TABLE ORDER BY datetime column it’s likely to be in the same order. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

If you are inserting with an ORDER BY clause on the date column, then you should end up with the identity column in the same order. Without an ORDER BY clause, no chance – not even if the datetime column is the PK of the source table. But it really shouldn’t be an issue – identity is useful only as a substitute key. If you need to rely on the value of the number, then you will have to generate your own values for it.

Thanks. The only best way is to use Order by in query. But once I did add identity column using EM and saw that the values were in jumbled order of DateTime column [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Adding the identity column to a table that already has data in it will result in no particular order of the identity values at all. Perhaps if all PK entries fit in one memory page, and you’ve reindexed immediately before adding the identity column? The only way would be to create a mirror table, with the identity column. You insert the data from the original table into the mirror table with an ORDER BY clause. You then drop all rows from the original table. Then you return the data from the mirror table into the original table, using the SET IDENTITY_INSERT <tbl_name> ON and SET IDENTITY_INSERT <tbl_name> OFF options. Meanwhile, you might run into the problem of cascading deletes, missing FK vlaues and the like – and still you have added a sequential series of values that is meaningless in itself and should not carry any understandable meaning anyway. There is a point where tidiness turns into a fear of stains.

Check the script EM generates in such cases. It doesn’t use an ORDER BY. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Thanks again. That happened two years ago when I was newbie to SQL Server [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Adriaan, I use intermediate table to do that task now-a-days. Yes Frank, EM does not use Order by [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

]]>