Tip: Resetting Identity Fields

  • Brandon Forest

    SSCommitted

    Points: 1806

    Comments posted to this topic are about the item Tip: Resetting Identity Fields

  • sknox

    SSChampion

    Points: 12284

    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

    SSCommitted

    Points: 1806

    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

    SSC Guru

    Points: 233804

    You can just obtain the max id value after toe deletion by using

    select max(col_id) from table_name

    Then use this to determine the reseed value.

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Pete Cox

    SSChasing Mays

    Points: 636

    Maybe I'm missing something here but why do you have an Identity column that you can abritrarily change the value of?

  • OCTom

    SSChampion

    Points: 11755

    My question is why do this? Why would anyone care if there are gaps in an identity field?

    Thank you,

    Tom

  • Verena_Techie_59

    SSC Rookie

    Points: 27

    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

    SSCrazy

    Points: 2049

    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

    SSCommitted

    Points: 1806

    @ 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

    SSC Rookie

    Points: 39

    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:

  • shenjane

    Grasshopper

    Points: 10

    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

    SSChampion

    Points: 11755

    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

    SSChampion

    Points: 11755

    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

    Old Hand

    Points: 303

    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

    SSChasing Mays

    Points: 636

    Shaking head. Moving on.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply