identity columns question

Question

identity columns question

jmartincano

SSC-Addicted

Points: 407
More actions
February 18, 2016 at 3:08 am

#308645

Hi all,
I am playing with identity columns and trying to find out a way to avoid the identity value being incremented for some "special" inserts. In MSSQL 2000 manuals I have found this comment and this seems also valid for 2012:
>>
Using the NOT FOR REPLICATION statement informs SQL Server 2000 that the replication process gets a waiver when supplying an explicit value and that the local identity value should not be reseeded. Each Publisher using this option receives a reseeding waiver.
>>
What I understood reading this and some 2012 stuff is that:
- You can define a specific range for the identity with:
CREATE TABLE test ( id INT IDENTITY (1, 1) NOT FOR REPLICATION PRIMARY KEY, name varchar (30) )
- If you insert with:
insert authors values ('AAA BBB')"
a new identity is generated and the identity is increased.
- If you insert with:
set identity_insert test on
insert test values (100000, 'CCC DDD')
set identity_insert test off
then value 100000 is inserted and the identity is not increased.
But with a simple test I see that doing the insert with setting identity_insert first makes the identity value increase to 100001.
I suppose I am missing a command and need to execute something more.
Any ideas ?, can someone help me ?.
Thanks a lot for your help.
Javier.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I suspect it's because you are not the replication process, and hence your inserts are treated normally,

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jmartincano SSC-Addicted Points: 407 More actions · Answer 2

I also think so, but my question is, the replication process would be executing something for this internally when applying those transactions, is there any way to get that ?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Could be anything, could be the procedures that the replication uses (which it creates itself), could be connection properties, could be a bunch of other stuff.

Why not create a stored proc that checks the current seed value, does your insert and then does a reseed to the previous seed value?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jmartincano SSC-Addicted Points: 407 More actions · Answer 4

That is a good I idea and was my first attempt indeed, basically I created this:

set @i = ident_current ('<table_name>')

set identity_insert <table_name> on

insert into <table_name> (id ...) values (1000000, ...)

set identity_insert <table_name> off

dbcc checkident ('<table_name>', reseed, @i)

And this works perfectly well for separate inserts with no concurrent users. If I run this in a loop and a normal insert in a diffent loop from another connection then the normal loop sometimes gets values out for its range.

Ken McKelvey SSCoach Points: 18867 More actions · Answer 5

As you seem to be using SQL2012, why not use a SEQUENCE instead of an IDENTITY?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Wrap it in a transaction and do the insert WITH TABLOCKX. Nasty for concurrency, but as long as you don't do this often may be fine.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass