CODEWITHSUNDEEP
sqlsql-serveridentityunique-key
Lee White
Lee White

Reputation: 3729

Sql Server: keep autoincrement from resetting when table is truncated

This question is basically the inverse from some other questions that have been asked.

I have a table with an incrementing identity, defined as follows:

[Instance_ID] [int] IDENTITY(1, 1) NOT NULL

This works fine. However, when I remove all records from this table and start inserting new ones, Instance_ID starts over from 1. This is causing quite some problems when I take extracts from this table for later use.

Is there a way to make sure that this particular column will never generate the same value twice, even after being truncated?

Upvotes: 1

Views: 1487

Answers (4)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

You can save current ident and use it when reseeding:

DECLARE @i INT = IDENT_CURRENT('tablename') + 1
TRUNCATE TABLE tablename
DBCC CHECKIDENT(tablename, RESEED, @i)

Upvotes: 2

shadow
shadow

Reputation: 1903

Declare @id int
Select @id = max(id)+1 from testtable
Truncate Table testtable
Dbcc CheckIdent (testtable, reseed, @id)

Upvotes: 0

Praveen ND
Praveen ND

Reputation: 560

Reseed your table 

DBCC CHECKIDENT (yourtable, reseed, 154)

Instead of 154 use the value from which you want the Instance_ID

Upvotes: 1

StackUser
StackUser

Reputation: 5398

Use Delete Table without where clause instead of truncating the table.

Try like this.

Delete tablename

Upvotes: 0

Related Questions