Remove IDENTITY Property From a Column

Most of the solutions for removing the IDENTITY property from a column in a SQL Server database table involve making a duplicate table (without the IDENTITY column property), copying the data from the original table to the new table, dropping the original table, and renaming the new table. This is effective. But it can be time consuming for large tables. So much so that a maintenance window might be required.

The time required to copy data from one table to another can be eliminated by using an ALTER TABLE SWITCH PARTITION operation. If you've ever used this option, you probably know there are a number of requirements. Surprisingly, matching IDENTITY columns is not one of them. Let's give it a try with the AdventureWorksDW2022 database and table dbo.FactCallCenter. To start, a "copy" of the original table is created. A matching Primary Key and a Foreign Key are created. If there were other constraints (Default Constraint, Check Constraint, etc.), non-clustered indexes, etc., we would create those as well.

USE AdventureWorksDW2022
GO
/****** Object:  Table [dbo].[FactCallCenter]    Script Date: 5/3/2025 4:10:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Create a "temp" copy of the original table without the IDENTITY property on [FactCallCenterID]
CREATE TABLE [dbo].[FactCallCenter_Temp] (
	--[FactCallCenterID] [int] IDENTITY(1,1) NOT NULL,
	[FactCallCenterID] [int] NOT NULL,
	[DateKey] [int] NOT NULL,
	[WageType] [nvarchar](15) NOT NULL,
	[Shift] [nvarchar](20) NOT NULL,
	[LevelOneOperators] [smallint] NOT NULL,
	[LevelTwoOperators] [smallint] NOT NULL,
	[TotalOperators] [smallint] NOT NULL,
	[Calls] [int] NOT NULL,
	[AutomaticResponses] [int] NOT NULL,
	[Orders] [int] NOT NULL,
	[IssuesRaised] [smallint] NOT NULL,
	[AverageTimePerIssue] [smallint] NOT NULL,
	[ServiceGrade] [float] NOT NULL,
	[Date] [datetime] NULL,
	CONSTRAINT [PK_FactCallCenter_Temp_FactCallCenterID] PRIMARY KEY CLUSTERED ([FactCallCenterID] ASC) WITH (
		PAD_INDEX = OFF,
		STATISTICS_NORECOMPUTE = OFF,
		IGNORE_DUP_KEY = OFF,
		ALLOW_ROW_LOCKS = ON,
		ALLOW_PAGE_LOCKS = ON
		)
	ON [PRIMARY],
		CONSTRAINT [AK_FactCallCenter_Temp_DateKey_Shift] UNIQUE NONCLUSTERED (
			[DateKey] ASC,
			[Shift] 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
ALTER TABLE [dbo].[FactCallCenter_Temp]
	WITH CHECK ADD CONSTRAINT [FK_FactCallCenter_Temp_DimDate] FOREIGN KEY ([DateKey]) REFERENCES [dbo].[DimDate]([DateKey])
GO
ALTER TABLE [dbo].[FactCallCenter_Temp] CHECK CONSTRAINT [FK_FactCallCenter_Temp_DimDate]
GO

With the "copy" of the table created, data is "moved" from the original table to the "temp" table via ALTER TABLE...SWITCH PARTITION, the original table is dropped, and the "temp" table is renamed to match the name of the original table:

ALTER TABLE dbo.FactCallCenter SWITCH PARTITION 1 TO dbo.FactCallCenter_Temp PARTITION 1;
DROP TABLE dbo.FactCallCenter;
EXEC sp_rename 'dbo.FactCallCenter_Temp',
	'FactCallCenter',
	'OBJECT';

Caveats and Gotchas

Referential integrity will get in the way. You'll need to drop & recreate foreign keys. If there are views with schema binding that reference the original table, you'll have to work around that too. Also, if there are authorizations on the original table (GRANT/DENY INSERT, UPDATE, DELETE, SELECT etc.), you'll have to recreate those authorizations on the IDENTITY-free table afterwards. There may be some other obstacles--those are just a few that I know of.