6

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ckpepper02
  • 3,297
  • 5
  • 29
  • 43
  • 1
    In this case, `TRUNCATE` will restart your identity column. If you use `DELETE`, then this won't happen – Lamak Jul 01 '13 at 15:52

3 Answers3

11

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.

GrandMasterFlush
  • 6,269
  • 19
  • 81
  • 104
  • 4
    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. – Grubsnik Mar 26 '15 at 10:14
7

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)
Robino
  • 4,530
  • 3
  • 37
  • 40
4

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.

Adrian Wragg
  • 7,311
  • 3
  • 26
  • 50