RESEED identity columns on the database

sql-server sql-server-2008 identity-column
Lorenzo picture Lorenzo · Nov 12, 2010 · Viewed 18.6k times · Source

Can I use the 

DBCC CHECKIDENT(<table_name>, RESEED, value)

command to reset an identity column current value to the original one in SQL Server 2008?

If yes, is this the correct way of doing this operation without having any drawback? If not, is there an alternate way of doing this?

Answer

Manfred Sorg picture Manfred Sorg · Nov 12, 2010

The value can be omitted. So if you use

DBCC CHECKIDENT (<table_name>, RESEED);

SQL Server sets the ident value to the correct next number - according to the numbers already in use. This is the only way to reseed identity values I know.

Related questions

SQL Server inserting huge number of rows to a table with default values and identity column in it

I need to insert about 6400000 rows a table with 2 columns CREATE TABLE [DBName].[DBO].[BigList] ( [All_ID] [int] identity(1,1) NOT NULL, [Is_It_Occupied] [int] default(0) not null ) I am using the following code today, which takes very long time …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More