SQL Server continue identity count after truncating table

Question

I seem to remember in MySQL when truncating a table the auto incremented index field would continue where it left off. So if said table was truncated with the highest id was 100, the next id after truncation would be 101.

Is there a way to do this in SQL Server? I truncated my table with over 1000 rows, but after truncating the next new id went back to 1 in my identity column. I would like for it to continue.

Acherman

In this case, TRUNCATE will restart your identity column. If you use DELETE, then this won't happen – Elaterite

Answer 1

DBCC CHECKIDENT (<table name>, reseed, 1000) should do the trick.

Note that the the reseed shown above will mean that the next number will be 1001, so set to 999 if you want the next ID to be 1000.

This article explains a bit more.

Pye

My testing on MSSQL2014 shows that if you seed it to 1000, the next entry will be 1000, not 1001. This is logically consistent with an identity(1,1) having a first entry of 1, not 2. – Taken

Note that in SQL Server 2016 and above, if you reseed to 1000, the next identity used will depend on whether there are any rows in the table. If the table has always been empty, or has been truncated, then the next value used will be 1000. If there are (or were) existing rows in the table, it will be 1001. See learn.microsoft.com/en-us/sql/t-sql/database-console-commands/… – Gal

Answer 2

Building on the answer from GrandMasterFlush, here is a script I use to achieve this "truncate but retain seed" functionality, assuming your id is bigint.

declare @id bigint
select @id = IDENT_CURRENT('MyTable')
print(@id)
truncate table MyTable
dbcc checkident (MyTable, reseed, @id)

Bute

As I've commented in the accepted answer, be careful with the reseed value after a truncation. IDENT_CURRENT(table) will return the current identity used and SQL will normally use the next incremented number for the next row. However, if the table is truncated, the current identity will be re-used for the next row inserted, so if you want to make sure your ids in this table do not clash with the old data, then you need to +1 to that @id value. See learn.microsoft.com/en-us/sql/t-sql/database-console-commands/… – Gal

Answer 3

From MSDN:

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

If you are set upon truncating the table, you can manually look up the maximum ID before truncating, and then reseed the table using DBCC CHECKIDENT.

Compote

Delete is very slow compared to truncate. – Bute