Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



Reseed Identity Column in a SQL Server Table
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-12-13







As per MSDN:

"IDENTITY (Property) (Transact-SQL)
Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.

Seed
Is the value that is used for the very first row loaded into the table.

Increment
Is the incremental value that is added to the identity value of the previous row that was loaded.
You must specify both the seed and increment or neither. If neither is specified, the default is (1,1)."

"DBCC CHECKIDENT
Checks the current identity value for the specified table in SQL Server 2016 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column."



Step 1: Create a Table


/****** Object: Table [dbo].[T1] Script Date: 12/13/2016 5:40:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T1](
[Col1] [int] IDENTITY(1,1) NOT NULL,
[Col2] [int] NULL,
[Col3] [int] NULL,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
[Col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO



Step 2: Insert some records

Insert into dbo.T1 (Col2, Col3) Values (1000, 5000)
Insert into dbo.T1 (Col2, Col3) Values (1000, 5000)
Insert into dbo.T1 (Col2, Col3) Values (1000, 5000)
Insert into dbo.T1 (Col2, Col3) Values (1000, 5000)
Insert into dbo.T1 (Col2, Col3) Values (1000, 5000)



Step 3: Check current Identity value

DBCC CHECKIDENT ('T1', NORESEED)

Output:
Checking identity information: current identity value '5', current column value '5'.


Step 4: Modify the current Identity value

DBCC CHECKIDENT ('T1', RESEED, 1000)



Step 5: Insert some records

Insert into dbo.T1 (Col2, Col3) Values (1000, 5000)
Insert into dbo.T1 (Col2, Col3) Values (1000, 5000)
Insert into dbo.T1 (Col2, Col3) Values (1000, 5000)
Insert into dbo.T1 (Col2, Col3) Values (1000, 5000)
Insert into dbo.T1 (Col2, Col3) Values (1000, 5000)


Step 6: Select all records

Select * from dbo.T1





Step 7: Check current Identity value

DBCC CHECKIDENT ('T1', NORESEED)

Output:
Checking identity information: current identity value '1005', current column value '1005'.