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

2 comments:

  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
  2. Excellent read, Positive site, where did u come up with the information on this posting? I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work,If anyone interested similar one's have a look here thanks.

    shih tzu puppies for sale near me
    buy puppy online
    fuhrerschein-kaufen-schweiz

    ReplyDelete