How to alter Auto Increment field in Sql server

It is not possible to remove Auto Increment field using Alter command in Sql Server.

What we can use is SET IDENTITY_INSERT

Take this following example this creates a table with auto increment field and still insert data to the same.

Generally if you try to insert the data into auto increment field it will through error.

But if we use SET IDENTITY_INSERT <Table Name>ON it will allow you to insert data into auto increment field.

Note: Make sure after completing this operation you set the value to SET IDENTITY_INSERT <Table Name>OFF

--Create table and its columns
CREATE TABLE [test].[Table_1] (
[Id][int] NOT NULL IDENTITY (1, 1),
[Name][nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL);
GO

ALTER TABLE [test].[Table_1] ADD CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ([Id])
GO

SET IDENTITY_INSERT [test].[Table_1] ON
GO
INSERT INTO [test].[Table_1] ([Id], [Name])
VALUES (1, 'Narender')

GO
INSERT INTO [test].[Table_1] ([Id], [Name])
VALUES (3, 'Rohit')

GO
INSERT INTO [test].[Table_1] ([Id], [Name])
VALUES (4, 'Mohit')

GO
INSERT INTO [test].[Table_1] ([Id], [Name])
VALUES (5, 'Shipra')

GO
SET IDENTITY_INSERT [test].[Table_1] OFF
GO

The above example simply insert the data into table without incrementing the values and keep the error shut..

If you try to insert any data after SET IDENTITY_INSERT [test].[Table_1] OFF it will through an error.

Share: