Post reply

Altering an Identity value on a existing table

  • August 8, 2006 at 4:26 am

    #61073

    Hi,

    Maybe it is niavety on my part but...

    How do you reset the initial value on an autoincremented IDENTITY on an existing table.

    The table has no values in it.

    My initial thoughts are to build a script to drop and create the table with

    [*****] [int] IDENTITY (1, 1) NOT NULL

    Is there a better way

  • August 8, 2006 at 4:31 am

    #653794

    if there are no rows present:

    DBCC CHECKIDENT (yourtablename, RESEED, 0)

    from the books online:

    DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) The 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 executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table

  • August 8, 2006 at 9:43 am

    #653876

    Another method:

    If the table isn't referenced by a foreign key constraint, you could TRUNCATE the table to reset the identitiy seed.

    Greg

    Greg

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

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