Does custom ID mess with auto-increment control? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Does custom ID mess with auto-increment control?

Author

Topic

yankleber
Starting Member

5 Posts

Posted - 2015-03-26 : 15:48:11

I have a table which identity auto-increment is set to ON.

Now once in a while I will have to insert some "special" records using a very high id, such as 1000001, 1000002, etc; but the "regular" records should keep using the regular sequential ids.

I know that I can set IDENTITY_INSERT ON/OFF, but my question is if the custom ids will mess with the auto-increment.

For instances suppose I already have in my table the ids 1,2,3,4 and 5. Then I set IDENTITY_INSERT ON and insert an id 1000, so now I will have 1,2,3,4,5 and 1000.

After I set IDENTITY_INSERT back to OFF the next auto-increment id will be 6 or 1001?

Thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 15:52:28

Easy to test:


create table #t (id int identity, a int)
insert into #t(a) values (1),(2),(3),(4),(5)

set identity_insert #t on

insert into #t (id, a) values (1000, 6)

set identity_insert #t off

insert into #t(a) values(7)

select * from #t

produces:

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-26 : 16:02:32

The identity value won't change until DBCC CHECK_IDENT is run, but you do have an impending collision issue when your ids make it up to that number. Inserts will start failing at that point and until the identity value is changed.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-26 : 16:06:46

Only if a unique index has been explicitly defined on the identity value. SQL doesn't automatically check for duplicate identity values, it simply assigns the next value.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-26 : 16:10:56

Btw, I thought SQL automatically reset if the value you specified was higher than the currently high identity value?!

If so, you'd want to:
1) begin a tran
2) lock the table
3) get the current hi ident
4) set ident insert on
5) add your rows
6) set ident insert on
7) reset ident to the value obtained in #3 above
8) commit tran to release all locks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-26 : 16:11:52

quote:
Originally posted by ScottPletcher

Only if a unique index has been explicitly defined on the identity value. SQL doesn't automatically check for duplicate identity values, it simply assigns the next value.

Oh yes! I'm so used to it being the PK that I forgot about that.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 19:22:42

@tara, @Scott you can see from my little test that the auto-increment jumped after inserting an explicit id to the next one after that (1001 in this case)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-26 : 19:34:11

quote:
Originally posted by gbritton

@tara, @Scott you can see from my little test that the auto-increment jumped after inserting an explicit id to the next one after that (1001 in this case)

I guess the OP will need to run DBCC CHECK_IDENT to reset it back to the lower number, but then there's the collision risk again.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

yankleber
Starting Member

5 Posts

Posted - 2015-03-26 : 19:35:59

Thank you gbritton, that's what I was afraid of. I think that I will have to duplicate the table and use one for the "regular" records and another for the special ones...

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 19:47:58

YOu could use negative numbers for the special ones. No collision risk, I think

alternatively, you could use a sequence object. more flexibility

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources