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


Tip: Resetting Identity Fields


Tip: Resetting Identity Fields

Author
Message
shenjane
shenjane
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 56
I am afraid that these two lines of code don't work when we delete some rows between Fld1=1 and Fld=10 instead of the last few rows as the example in the article.

Let's try below scripts:
......
//delete these three rows Fld1 3,4,5
delete from TestReseed where Fld1 between 3 and 5

//execute those two lines of DBCC code
dbcc checkident("TestReseed", reseed,1)
dbcc checkident("TestReseed", reseed)

//insert three rows back
insert into TestReseed(Fld2) values (3)
insert into TestReseed(Fld2) values (4)
insert into TestReseed(Fld2) values (5)
....
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: 4141 Visits: 4152
Verena_Techie_59 (12/3/2012)
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!


OK. Thanks. I appreciate your answer.
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: 4141 Visits: 4152
Brandon Forest (12/3/2012)
@ 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


Who is complaining? I asked why because I truly wanted to know. I never thought to be concerned with gaps.
markjholmes
markjholmes
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 181
I've had to deal with a 32bit identity value that starts at 1, design and business changes mean that the expected range of a few million is now several billion...and within a few years, 2^32 is exceeded. Reseed at minvalue of 32-bit int, and you still have 2 billion values ... which becomes a problem in a year because growth is exponential.

There is a use case for this - sometimes it's a bandaid approach to stave off disaster (i.e. you start with a few thousand customers and sales go crazy, suddenly you have a million customers - extended downtime is not an option). Sometimes it's just a case of data changes - remember the golden rule of SQL: "It depends." Nobody runs their database the same as the next guy, so a few of you should be careful not to say this is never an option. Sometimes it is, but there's often a tradeoff. I can tell you from experience that if because of growth you have to reseed twice or three times - you really want a solid plan to permanently fix it. I've had to clean up the fallout from others' quick fixes.
Pete Cox
Pete Cox
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 277
Shaking head. Moving on.
DiverKas
DiverKas
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 460
Brandon Forest (12/3/2012)
@ 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


Fine. BUT if you are going to show something that is quite out of the ordinary, disclaimers should be used. Complete code should be used and a full explanation of why someone might do something would be great.

There are several levels of readers here, and giving them half a story, with something that has so many pitfalls is fraught with danger. Those that have more complete grasp on the ramifications get it, but not everyone does and its a disservice to those that dont.
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