Post reply

Need help on reseed Identity column - urgent..

  • July 10, 2007 at 9:13 am

    #117369

    Below is our project scenario:

     We have 11 tables [each with millions of data] joined by a partitioned view. The identity value is split across 11 tables. i.e first table will have seed=1 and increment=8, second table will have seed=2 and increment=8, ninth table will have seed= -1 and increment=8 and so on. By this way we have gap to add 5 more tables using the current identity value [identity column is of INT datatype]. In couple of tables the identity value is reaching its maximum INT value. We came with solution of reseeding since we have gap to add 5 more tables. No other solution seems to be successful with millions of production data with maximum allowed downtime of 2 days.

     

    Command we issued:

    DBCC CHECKIDENT (Claim_GC_1136_1zzz, RESEED,-2147483648)

     

    Current maximum Identity value in the table – 666666848. Obviously new identity value is smaller than the original. I know when we issue the above command without second parameter, the identity will be automatically reset to the current maximum value. But I do not want that to happen. Now my question is: When I reseed to negative value, is there any chance of seed getting reverted back automatically? In the above scenario how reliable is this solution?

     

    I am not getting any error message on executing the statement. I get something of this sort which seems to be fine.

    Checking identity information: current identity value '11', current column value '-999999'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

     

    On strange behavior I could notice is:

    We checked the table immediate next day and there was no trace of what we did the previous day. Both the new identity value and newly inserted rows got disappeared!  But I should say that it doesn’t happen always. Rarely it is getting reset. Otherwise, the reseed which I executed two weeks back seems to be fine so far without any problems.

    I wanted to know how reliable this solution is. Am I missing something in the way I execute the command? Is there any chance of some database process resetting the seed automatically in order to maintain the table integrity?

  • July 10, 2007 at 9:26 am

    #718329

    Hi

    Maybe I'm missing the obvious here...but why don't you use bigint?

    From BOL: "The bigint data type is supported where integer values are supported. However, bigint is intended for special cases where the integer values may exceed the range supported by the int data type."

    bigint

    Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

    int

    Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

    Just a thought...

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • July 14, 2007 at 10:09 pm

    #719501

    I'd like to know why this particular method of "partioning" was selected...

    ...and with 11 tables, I'm thinking that an increment of only 8 is going to create some duplication of numbers unless some of the increments are negative

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 17, 2007 at 1:07 am

    #719895

    Yes, I'm very curious as well about this strange way of setting up the identity columns. Indeed, -1,7,15,23,... is the same as 7,15,23,31,... with the exception of the first element. What's the point here?

Viewing 4 posts - 1 through 3 (of 3 total)

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