SQL Server continue identity count after truncating table

sql-server identity-column
ckpepper02 picture ckpepper02 · Jul 1, 2013 · Viewed 10.4k times · Source

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.

Answer

GrandMasterFlush picture GrandMasterFlush · Jul 1, 2013

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.

Related questions

Adding an identity to an existing column

I need to change the primary key of a table to an identity column, and there's already a number of rows in table. I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine …

Read More
How do you determine what SQL Tables have an identity column programmatically

I want to create a list of columns in SQL Server 2005 that have identity columns and their corresponding table in T-SQL. Results would be something like: TableName, ColumnName

Read More
BULK INSERT with identity (auto-increment) column

I am trying to add bulk data in database from CSV file. Employee table has a column ID (PK) auto-incremented. CREATE TABLE [dbo].[Employee]( [id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL, [Address] [varchar](50) NULL ) ON [PRIMARY] I am using …

Read More