How to Drop Identity Property of a Column in SQL Server Table - SQL Server / T-SQL Tutorial 44

In this post we will learn how to drop identity property of a column in SQL Server Table.

We will follow below steps.

  • Add a new column with TestID to existing Table
  • Update the records from Id ( Identity enable Column) to TestID (Newly Added) Column.
  • Drop the Id ( Identity Enable Column) from Table
  • Rename the Newly Added Column ( TestID) to Id.
--Create Table with Identity Property
CREATE TABLE dbo.Employee ( Id INT IDENTITY(1,1), Name VARCHAR(10))

--Insert the record after creating Table with Identity Property on Id Column

INSERT INTO dbo.Employee 

--Run to See the Data

SELECT * FROM dbo.Employee

--Find out all the columns for all the tables on which Identity Property is enabled

SELECT OBJECT_NAME(OBJECT_ID) AS TableName,name AS ColumnName FROM sys.columns
WHERE is_identity=1

/** Drop Identity ********/
--Add a new column with any name

ALTER TABLE dbo.Employee

--Update the Records in newly Added column , in our case TestID
UPDATE dbo.Employee
SET TestId=Id

--Drop Identity Column

ALTER TABLE dbo.Employee

--Rename the newly Added Column to Identity Column you had at first.

EXEC sp_rename 'dbo.Employee.TestId','Id','COLUMN'             

Video Demo : How to drop Identity Property of a Column in SQL Server Table


  1. I am happy to obtain qualified online brokers support when getting involved in business sale deals. Actually, you're provided with a proven strategy when getting in touch with websiteclosers businesses brokers since they deliver a customer-oriented approach based on your demands and expectations.

  2. Anyone have tips for maintaining a water heater to extend its lifespan? water heater replacement plano

  3. Upgrading to a tankless water heater saved me both money and space. water heater replacement santa fe new mexico

  4. Updating your water heater is a wise investment in home improvement. water heater replacement berkeley

  5. Restoring a property's foundation is a critical step in ensuring its stability.
    Property Restoration Services