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'.
|
|
|
|
|
|