SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tip: Resetting Identity Fields


Tip: Resetting Identity Fields

Author
Message
Brandon Forest
Brandon Forest
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 446
Comments posted to this topic are about the item Tip: Resetting Identity Fields
sknox
sknox
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3922 Visits: 2920
So you've essentially saved having to declare and set a value in a variable. But to do so, you've had to run an extra DBCC command.

It would be interesting to see how the two different approaches compare:
a) performance-wise against a large table with lots of gaps, and
b) stability-wise against an active table (i.e, one with many connections).

Perhaps if I have a few minutes tomorrow I might test...
Brandon Forest
Brandon Forest
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 446
My intent was code simplicity. There is no "One Right Way" to code, but some ways are better than others. I will be interested in seeing your results. ;-)
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51210 Visits: 17653
You can just obtain the max id value after toe deletion by using

[code="sql']select max(col_id) from table_name[/code]

Then use this to determine the reseed value.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Pete Cox
Pete Cox
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 277
Maybe I'm missing something here but why do you have an Identity column that you can abritrarily change the value of?
OCTom
OCTom
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4061 Visits: 4152
My question is why do this? Why would anyone care if there are gaps in an identity field?

Thank you,
Tom
Verena_Techie_59
Verena_Techie_59
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 14
There are several reasons for congiguous identity keys.

1) It's much simpler to keep ID keys in sync between production and testing systems.

2) Coding can be simpler and more reliable. In testing I can add the rows, then delete where ID > (the max key before additions) then reset the ID key again. This is probably the best reason. When I am adding rows to a table. Of course no one else better be adding rows at the same time or it won't work anyway and I'll have to code to find the records I just added and then select the max key to reset back to.

3) It's clean.

4) What if your id keys don't go up by 1? You could run out of ID keys fast, having very large gaps and an application may not be able to handle keys that are larger than x.

5) Some designs depend on certain values for identity fields, probably a poor design mind you....

6) Some gaps are better left. Especially when cleaning up some data that has been live. There may be history attached to it that will be kept. I don't like to reuse keys in case someone coded against the ID's and some programmers have....

FYI: to do this for Sybase:

Select max(id) key from table (either before inserts or after deletions) and then use sp_chgattribute to reset the identity key back.

select max(table1_id) from database..table1
-- example 300

use database1
sp_chgattribute table1, 'identity_burn_max', 0, '300'

7) Reason 7 is for us OCD types!
DiverKas
DiverKas
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 460
Sigh... ID's like this should NOT be intelligent, by their very nature they should tell NOTHING of the data. It serves to uniquely identify a row. Thats it.

Second, any code like this that changes the keys (never should be done, but since we are showing how) needs to be wrapped in a transaction. Otherwise, the risk of assigning the seed to a key that has been used is pretty high.

Third, this only works if they key isnt a FK somewhere else. Otherwise you face the unenviable task of updating all those rows as well.

This is on my list of things you should never contemplate doing.
Brandon Forest
Brandon Forest
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 446
@ those who question why to do this. It's very simple, if you don't see the need to do it, don't do it. Questioning why someone else chooses to do something is their business not yours. If you don't agree or approve, simply shake your head and say "Bullshit" and move on. No one is interested in hearing you complain. Focus on the technique, not the why. Wrapping it in a transaction is a good idea. Why we do it is our business. Wow
Vladko
Vladko
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 137
This is my approach - first coping all records to destination table and using the script below to clean the source and preserve identity so the next batch run will not create duplication's at the destination. The source table holds auditing data and it's growing fast and getting big (10gb per week if I do nothing). This way I can offload the table and export the records to remote database for further investigations.


USE [Mydatabase]

declare @ident bigint;
select @ident = IDENT_CURRENT('dbo.mytable')
set @ident = @ident + 1

truncate table dbo.mytable

DBCC CHECKIDENT ("dbo.mytable", RESEED, @ident)


============================================
Life is SQLized... Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search