IDENTITY Column - and the steps to remove from your table

Issue:

Your table has a column identified as an IDENTITY (autonumber) column, and you need to

1) keep the column and its contents
2) remove the IDENTITY attribute for the column.

Approach:

As of 2017, SQL Server does not have an ALTER TABLE command that allows you to remove the IDENTITY property. The basic steps to remove an IDENTITY property are as follows:

- add a new column, and copy contents into the new column
- drop any indexes and constraints using the TO BE dropped column
- drop the old column
- rename the new column with the old name
- rebuild indexes and constraints

Here is a sample set up scripts to get the job done:

The first part creates a test table, the second part removes the IDENTITY property.

-------------------------------------------------------
-- setup:

use work;
go

create table work.dbo.testidentity
(  tblid     int identity(1,1) not null
  ,somevalue varchar(20) null
  );

alter table work.dbo.testidentity add constraint pk_tblid  primary key clustered ( tblID);

  insert into  work.dbo.testidentity (somevalue)
  values ( 'one' )
,( 'two' )
,( 'three' )
,( 'four' );

select * from work.dbo.testidentity ;

-------------------------------------------------------
-- the process to remove:

alter table work.dbo.testidentity add temp_tblID int null;
go
select * from work.dbo.testidentity ;
go

update work.dbo.testidentity set temp_tblID = tblid;
go
alter table work.dbo.testidentity alter column temp_tblID int not null;
go
select * from work.dbo.testidentity ;
go
alter table work.dbo.testidentity drop  constraint pk_tblid;
go
alter table work.dbo.testidentity  drop column tblid ;
go
select * from work.dbo.testidentity ;
go
EXEC sp_rename 'dbo.testidentity.temp_tblID' , 'tblID', 'COLUMN';
go
select * from work.dbo.testidentity ;
go

alter table work.dbo.testidentity add constraint pk_tblid  primary key clustered ( tblID);
go