identity column

  • Hi all,

    I am having one primary column empid (Numeric) in my table.

    for that column i set the identity yes option.

    Here my qtn is whenever i inserted into table,the empid value is incresing one by one.What i am facing the problem is ,lets say ,i have added 1000 rows into the table.immediately i have deleted all.

    When i add again, the value is starting with 1001,1002...

    Is there any option where i can reset the value to 1,2...?

    Hope u would have understand!.

    Pls help me on this!.

    Thanks in Advance!

    -Ramesh.

  • check for DBCC CHECKIDENT in BOL.

    eg:

    DBCC CHECKIDENT (Employee, RESEED, 1)

  • DBCC CHECKIDENT will create a duplicate Identity values

    Ex. Id_no, Name

    1 Test1

    2 Test2

    3 Test3

    4 Test4

    If you delete rows with Id_no 1 and 2 and

    Issue a DBCC CHECKIDENT(Table_Name, RESEED, 1)

    Next time when you insert 3 rows your table would look like

    Id_no, Name

    1 Test_New1

    2 Test_New2

    3 Test_New3

    3 Test3

    4 Test4

    It does not skip existing Identity values

    You can write a script to identify missing Identity values and

    use these values during your data insertion, but this is not practical

    The best solution is to re-build the identity column

    by dropping and re-creating it after data insertion

    MW

    Edited by - mworku on 06/18/2003 3:13:17 PM


    MW

  • he deleted all the rows, so i dont see any problem with reseeding it to 1.

  • You can use TRUNCATE <tablename> to delete all records.

    The identity column value is reset to 1 for the next insertion.

    Claude

  • Actually, it's Truncate TABLE <Table Name>, and that's definitely the way to do it. If you have any foreign keys that reference that table you'll have to drop them first.

    cheers.

    cl

    Signature is NULL

  • 
    
    BEGIN TRAN
    DBCC CHECKIDENT('Table1',RESEED,1)
    DBCC CHECKIDENT('Table1',RESEED)
    COMMIT TRAN

    This will reset to the next non-duplicate value. The RESEED option will only reset the next-in-sequence upward, so set to 1, then let the RESEED do it's work.

    -Dan


    -Dan

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

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