adjust identity gaps - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

adjust identity gaps

Author

Topic

sqldev05
Starting Member

9 Posts

Posted - 2008-04-06 : 16:36:10

Hi,

I have a table with an identity column..How will the identity gaps be adjusted if i delete few records in the table..ie..the sequence should automatically adjusted..Is there any way for this ?

ID Name City
1 abc xyz
2 mexm mcel
3 olekc kcome

Suppose i delete the record where ID=2..still the sequence should be auto adjusted..ie.the record of ID=3 should become ID=2 automatically..there shouldn't be any gaps.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-06 : 16:41:42

No, sql doesn't do that automatically. But you can reset it with 'dbcc checkident(tab_name, reseed)'.

sqldev05
Starting Member

9 Posts

Posted - 2008-04-06 : 20:20:21

Hi,

Thanks for your reply.
But 'dbcc checkident(tab_name, reseed) is used when the highest identity value record gets deleted. It doesn't fill the gaps when i deleted records in a random manner.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-06 : 23:27:15

There is no easy way to fill the gaps. And you shouldn't bother with it anyway. Why do you care to fill it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-07 : 03:59:12

Maybe he is afraid that the approx 2 billion positive available numbers are going to run out?

E 12°55'05.25"
N 56°04'39.16"

yaman
Posting Yak Master

213 Posts

Posted - 2008-04-07 : 08:49:40

First u delete the records of table then after Run this Query

DBCC CHECKIDENT('Table name ', RESEED, 0)

or

if u want to insert record Explicitly then used

SET IDENTITY_INSERT TblEmployeDetail ON

INSERT INTO TblEmployeDetail (eid,Ename,Esal,Epan,Emobile)
values(2,'Baasnk',102000,'11212121','1212121')

SET IDENTITY_INSERT TblEmployeDetail OFf

Yaman

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-07 : 08:55:17

Great. What if there are foreign key contraints to this table?
I and tkizer are referring to this. Leave ID as is.
There are 2 billion more.

E 12°55'05.25"
N 56°04'39.16"

yaman
Posting Yak Master

213 Posts

Posted - 2008-04-07 : 09:06:44

U also run this query with out Delete the records

DBCC CHECKIDENT('TblEmployeDetail', RESEED, Number u want to reset)

Yaman

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-07 : 09:36:54

And now we are back to the original question.
What about GAPS?

1
2
6
7

If you delete last inserted record (#7) you can easily use DBCC CHECKIDENT to reclaim #7 again.
But the original question was about GAPS, ie #3-#5.

Original poster wants to reclaim them too.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources