find a location for property in a new city

Wednesday, 27 October 2010

Set an identity seed back after delete in SQL

When deleting from a table in SQL the seed of the identity column or primary key will not be forgotten. It would be nice to truncate the selected rows(?). This is a little annoying but there is a way around it.

If you want to quickly remove all rows from a table and begin the table from scratch TRUNCATE is the best approach. It is quicker too since it logs nothing (so beware).

TRUNCATE is only for destroying all data in a table though. What if you want to remove only the last few rows and then begin the seeding off from the last primary key?

Use CHECKIDENT to reseed

You can delete the rows you want and after you can reset the SEED of the table to what you desire using the following line:
DBCC CHECKIDENT('myTable', RESEED, 10)

Note: the 10 is a seed so you would set this to be the current maximum identity. So if you reseed with 10 the next insert will yield 11 as the primary key.

Follow britishdev on Twitter

1 comment:

  1. Hope this helps someone:

    -- Get the seed in case of rollback.
    SELECT @tbl_Seed = (MAX(ID)) FROM [tbl]

    -- Do stuff...

    -- Upon rollback
    IF NOT @tbl_Seed IS NULL BEGIN
    PRINT ''
    PRINT 'Reseeding [tbl] to original identity seed...'
    DBCC CHECKIDENT ('[tbl]', RESEED, @tbl_Seed);
    END

    ReplyDelete