When a new record is added to a table, SQL Server generates a unique number using the IDENTITY keyword. This is the primary key field where we want to insert a sequence number or a row number to address that specific row.
The IDENTITY can be specified with a starting value and an incremental value, both of which must be numerical values. If no starting and incremental values are specified, SQL Server will allocate (1,1), which means the first value will be 1 and incremented by 1.
Because the column is auto-generated, it should be skipped from the INSERT statement once it has been assigned with IDENTITY.
Once assigned the identity value cannot be modified however there’s an alternative. Look at the below example.
— Creating the table with Auto-generated values.
CREATE TABLE [SampleData] (
[IDCol] INT IDENTITY(201801,1),
[StringCol] VARCHAR(100) DEFAULT ‘BigDatanSQL’,
[DateCol] DATETIME DEFAULT GETDATE());
GO
— Inserting 12 rows, in loop.
INSERT INTO [SampleData] DEFAULT VALUES;
GO 12
SELECT * FROM SampleData
— Now, if the identity value needs to be modified to something else, then
SET IDENTITY_INSERT SampleData ON
INSERT INTO SampleData(IDCol)VALUES(201901)
SET IDENTITY_INSERT SampleData OFF
GO
— Inserting 11 rows, in loop.
INSERT INTO [SampleData] DEFAULT VALUES;
GO 11
SELECT * FROM SampleData
Hope you find this post helpful.
Big Data and SQL 
One comment