How to re-sow table ID in SQL Server 2008 and undo it all safely?

Question

I need to do this just for testing, but then cancel it when the test is complete.

I saw several online tutorials on how to re-sow a table, but not so much on how to cancel it.

Let's say the table definition is as follows:

create table beer ( beer_id numeric(10) not null, mnemonic nvarchar(8) ); go 

Let's say that I want new identifiers to temporarily start with 12345 , and at the end delete new lines and set the next identity to what would be.

Answer 1

The reset command has the property

 DBCC CHECKIDENT (tablename, RESEED, new_reseed_value) 

If you want to set the column id to 12345, you run this

 DBCC CHECKIDENT (beer, RESEED, 12345) 

If you want to delete the test lines and restore the value to the previous value, follow these steps:

 DELETE FROM beer WHERE beer_id >= 12345 ; DECLARE @NewSeed NUMERIC(10) SELECT @NewSeed = MAX(beer_id) FROM beer ; DBCC CHECKIDENT (beer, RESEED, @NewSeed) 

Here is a demonstration of your scenario. Note that the beer_id column is created with the IDENTITY (1, 1) property, which assigns the identifier 1 in increments of 1.

 CREATE TABLE beer ( beer_id NUMERIC(10) IDENTITY (1,1) NOT NULL, mnemonic NVARCHAR(8) ); GO INSERT INTO beer(mnemonic) VALUES ('Beer 1') INSERT INTO beer(mnemonic) VALUES ('Beer 2') SELECT * FROM beer ; DBCC CHECKIDENT (beer, RESEED, 12345) GO INSERT INTO beer(mnemonic) VALUES ('Beer 3') INSERT INTO beer(mnemonic) VALUES ('Beer 4') SELECT * FROM beer ; DELETE FROM beer WHERE beer_id >= 12345 ; DECLARE @NewSeed NUMERIC(10) SELECT @NewSeed = MAX(beer_id) FROM beer ; DBCC CHECKIDENT (beer, RESEED, @NewSeed) GO INSERT INTO beer(mnemonic) VALUES ('Beer 5') INSERT INTO beer(mnemonic) VALUES ('Beer 6') SELECT * FROM beer ; 

Answer 2

Sometimes we have a Schema restriction from some users, which leads to an error ( it is impossible to find a table or object with the name "TableName". Check the system directory ), so it is best to follow the code below.

Schema.TableName must be enclosed in an apostrophe

 DECLARE @SeedValue INT SET @SeedValue = (SELECT MAX(ColumnName) FROM Schema.TableName) DBCC CHECKIDENT ('Schema.TableName',RESEED,@SeedValue)