Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Tip: Resetting Identity Fields Expand / Collapse
Author
Message
Posted Monday, December 03, 2012 2:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 4:28 PM
Points: 2, Visits: 30
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)
....
Post #1392138
Posted Monday, December 03, 2012 2:23 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 2,409, Visits: 3,435
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.
Post #1392144
Posted Monday, December 03, 2012 2:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 2,409, Visits: 3,435
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.


Who is complaining? I asked why because I truly wanted to know. I never thought to be concerned with gaps.
Post #1392145
Posted Monday, December 03, 2012 7:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 03, 2014 9:30 AM
Points: 68, Visits: 133
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.

Post #1392224
Posted Tuesday, December 04, 2012 1:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 14, 2013 1:05 AM
Points: 96, Visits: 277
Shaking head. Moving on.
Post #1392280
Posted Tuesday, December 04, 2012 7:46 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, 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.


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.

Post #1392497
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse