April 28, 2010 at 7:37 am
Hi all.
A colleague of mine told me about an unusual problem that he encountered. Well, it's not really a problem, it's just a bit strange and of course I want to find out why :-D.
The problem is that when a table with an identity constraint is loaded for the very first time, but the first records are inserted with identity_insert on (for negative values. This is done for having some dummy records in the dimension). For some reason, the identity skips the number 1. This is done on SQL Server 2005.
My colleague wrote a script to illustrate the problem:
-- For a better viewing experience, execute one step at a time :-)
--
-- step 1: create test table
--
if object_id('tempdb..#aa') is not null drop table #aa
create table #aa (id int not null primary key identity(1,1)) --> seed is 1
--
-- step 2: insert dummy rows
--
set identity_insert #aa on
insert into #aa(id) select -33 union select -66 union select -99
set identity_insert #aa off
--
-- step 3: insert regular rows
--
insert into #aa default values --> first identity insert, id SHOULD be 1
insert into #aa default values --> next identity insert, id SHOULD be 2
--
-- step 4: check results
--
select * from #aa --> there you go, a mystery :-/ Stand by, more to come!
--
-- step 5: is this caused by inserting dummy rows? Let's add more dummies:
--
set identity_insert #aa on
insert into #aa(id) select -100 union select -110 union select -120
set identity_insert #aa off
--
-- step 6: insert more rows
--
insert into #aa default values --> next identity insert
insert into #aa default values --> next identity insert
--
-- step 7: check results
--
select * from #aa --> hmmm mystery number 2 :-o
--
-- step 8: can we repeat mystery number 1 ?
--
truncate table #aa -- and return to step 2
Is there some sort of option that causes this behaviour? My best guess is that the system caches the first value (which is 1), because it assumes that it will be used first. But, because the negative values inserted with identity_insert, it has to drop this cached value. Since the value 1 has then already been "used", it will take the value 2 instead.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 28, 2010 at 8:30 am
What's more interesting is that if I do this:
set identity_insert #aa on
insert into #aa(id) select 8
set identity_insert #aa off
insert into #aa default values --> first identity insert, id SHOULD be 1
I get a 8 and 9 in the table.
Digging in more now.
April 28, 2010 at 8:45 am
Steve Jones - Editor (4/28/2010)
What's more interesting is that if I do this:
set identity_insert #aa oninsert into #aa(id) select 8
set identity_insert #aa off
insert into #aa default values --> first identity insert, id SHOULD be 1
I get a 8 and 9 in the table.
Digging in more now.
I might be missing something, but i thought that this was the intedend behaviour of indentity_insert
from BOL
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value
April 28, 2010 at 9:08 am
I suppose a reseed is done when IDENTITY_INSERT is turned off. Maybe this behaviour can be derived from the description of DBCC CHECKIDENT in BOL:
DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )
Current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, or all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.
and
The following table lists conditions when DBCC CHECKIDENT does not automatically reset the current identity value and provides methods for resetting the value.
Condition Reset methods
The current identity value is larger than the maximum value in the table.
If records are added with a value smaller then the base seed (with IDENTITY_INSERT on), new records (with IDENTITY_INSERT off) are added with a value of the base seed (1) + increment (1). If the table is truncated, new records are inserted with a value of the base seed (1). Just try a TRUNCATE TABLE #aa after step 2 to see this behaviour.
HTH,
Peter
April 28, 2010 at 9:17 am
Yes, that's what it's supposed to do. Didn't realize that, but it's listed here in the SET IDENTITY_INSERT page. So if you insert a larger value, it should update things.
However the negatives first appears to be a bug. When you insert a negative, the first value stored in sys.identity_columns changes from null to 1, despite the fact that the ident_current stays at 1.
What's different here, is that if I just insert a default first, while sys.identity_columns changes from null to 1, ident_current stays at 1. The second insert moves that to 2, while inserting a value of 2.
What I ran:
if object_id('aa') is not null drop table aa
create table aa (id int not null primary key identity(1,1)) --> seed is 1
SELECT IDENT_CURRENT('aa')
-- 1
set identity_insert aa on
insert into aa(id) select 8
set identity_insert aa off
SELECT IDENT_CURRENT('aa')
-- 8
insert into aa default values --> first identity insert, id SHOULD be 1
SELECT IDENT_CURRENT('aa')
-- 9, table has values 8 and 9
SELECT * FROM aa
if object_id('aa') is not null drop table aa
create table aa (id int not null primary key identity(1,1)) --> seed is 1
SELECT IDENT_CURRENT('aa')
-- 1
SELECT i.last_value, i.*
FROM sys.identity_columns i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.name = 'aa'
-- null
set identity_insert aa on
insert into aa(id) select -10
set identity_insert aa off
SELECT IDENT_CURRENT('aa')
-- 1
SELECT i.last_value, i.*
FROM sys.identity_columns i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.name = 'aa'
-- 1
if object_id('aa') is not null drop table aa
create table aa (id int not null primary key identity(1,1)) --> seed is 1
SELECT IDENT_CURRENT('aa')
-- 1
SELECT i.last_value, i.*
FROM sys.identity_columns i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.name = 'aa'
-- null
insert into aa default values --> next identity insert, id SHOULD be 2
SELECT IDENT_CURRENT('aa')
-- 1
SELECT i.last_value, i.*
FROM sys.identity_columns i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.name = 'aa'
-- null
SELECT * FROM aa
April 28, 2010 at 9:40 am
Noted as fixed in the next version (SQL 11, I think).
April 29, 2010 at 12:40 am
Thanks all for the (very quick) responses. Now I can sleep at night :-D.
I did some research on Google though, before posting this question, but I didn't find any plausible answer. Only some sites of people who observed the same behaviour. Ah well, maybe I have to polish up my Google skills 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply