Post reply

DBCC CHECKIDENT

  • May 14, 2008 at 2:21 am

    #182427

    Hi All,

    I am running the following code on a table.

    The ID field is Identity(1,1)

    The table has 5 rows with ID's [1-5]

    When I run:

    DBCC CHECKIDENT ([MyTable],reseed,0)

    And then do an insert I get a PK Violation.

    However if I run :

    DBCC CHECKIDENT ([MyTable],reseed,6)

    Then it works.

    Please could someone tell me why the first line doesn't work?

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • May 14, 2008 at 2:27 am

    #814977

    Books Online

    DBCC CHECKIDENT ( 'table_name', RESEED, new_reseed_value )

    Current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.

    Hence, the insert after you reseed to 0 attempt to put in a value of 1, which is already there. If you want 0, you'll have to reseed to -1

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 14, 2008 at 2:37 am

    #814983

    Thanks for the reply.

    That what I would expect to happen.

    The current table count is 5.

    So if I make it 0 it should be 5+1 which is 6.

    I have tried the same code on other tables and it works 100%

    MyTable2

    ID IDENTITY(1,1)

    Row count 68

    DBCC CHECKIDENT(Mytable2,0)

    If I do an insert it works great?!?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • May 14, 2008 at 2:42 am

    #814985

    Sorry I made a mistake with my last set of Data

    Mytable2

    has a row count of 67 with the largest Identity value of 68

    Does the row count actually make any difference, I wouldn't think that it does?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • May 14, 2008 at 2:48 am

    #814992

    SILLY SILLY SILLY

    Me

    what I have done is add:

    DBBCC CHECKIDENT(Mytable,reseed)

    After I reseed it to 0 so that it corrects it's self.

    Thanks for all the help 🙂

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • May 14, 2008 at 2:50 am

    #814996

    Christopher Stobbs (5/14/2008)

    Thanks for the reply.

    That what I would expect to happen.

    The current table count is 5.

    So if I make it 0 it should be 5+1 which is 6.

    No, if you make it 0 then the next value inserted is new reseed value (0) + current increment value (1), which is 1

    Edit: Glad you're sorted.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

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