question about identity_insert

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 2000 Forums

SQL Server Development (2000)

question about identity_insert

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-01-21 : 11:21:28

when i use :


set identity_insert mytable on

and i then turn it off will the autoincremnet will continue from the last most high ID?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-21 : 12:08:04

It will continue from the current seed.
If the last value inserted is greater than the seed then the seed will be set to the new value.

You can check (and set) the seed via dbcc checkident.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-01-21 : 12:11:50

thnaks alot

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-01-22 : 04:22:14

1 more question :
if i do :


DBCC CHECKIDENT ('table1', RESEED, 30)

and i have in the tables id's from 35 to 40
and i start to do insert will it override the existing ones or skip them?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 04:29:34

[code]create table #seed (i int identity(1, 1), data varchar(10))

insert #seed (data)
select 'peso'

select * from #seed

set identity_insert #seed on

insert #seed (i, data)
select 10, 'peleg2k'

select * from #seed

set identity_insert #seed off

insert #seed (data)
select 'sqlteam'

select * from #seed

DBCC CHECKIDENT ('#seed', RESEED, 9)

insert #seed (data)
select 'test'

select * from #seed

drop table #seed[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 04:32:11

And now try this

create table #seed (i int identity(1, 1) primary key, data varchar(10))

insert	#seed (data)
select	'peso'

select * from #seed

set identity_insert #seed on

insert	#seed (i, data)
select	10, 'peleg2k'

select * from #seed

set identity_insert #seed off

insert	#seed (data)
select	'sqlteam'

select * from #seed

DBCC CHECKIDENT ('#seed', RESEED, 9)

insert	#seed (data)
select	'test'

select * from #seed

drop table #seed

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-22 : 10:53:44

>> It will continue from the current seed.
It updates the seed on insert and takes the next value for the next identity.
It doesn't look to see what is in the table.

Identities aren't guaranteed to be unique or consecutive.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-01-23 : 01:52:53

quote:
Identities aren't guaranteed to be unique or consecutive.

what do u mean by that? that i can recive a duplicate id????

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 01:54:52

quote:
Originally posted by pelegk2

what do u mean by that? that i can recive a duplicate id????

If you even bothered to run the script I posted, you would have noticed that by now...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources