Remove Identity from a column in a table

Question

We have a 5GB table (nearly 500 million rows) and we want to remove the identity property on one of the column, but when we try to do this through SSMS - it times out.

Can this be done through T-SQL?

Answer 1

I just had this same problem. 4 statements in SSMS instead of using the GUI and it was very fast.

• Make a new column

  alter table users add newusernum int;

• Copy values over

  update users set newusernum=usernum;

• Drop the old column

  alter table users drop column usernum;

• Rename the new column to the old column name

  EXEC sp_RENAME 'users.newusernum' , 'usernum', 'COLUMN';

Answer 2

You cannot remove an IDENTITY specification once set.

To remove the entire column:

ALTER TABLE yourTable
DROP COLUMN yourCOlumn;

Information about ALTER TABLE here

If you need to keep the data, but remove the IDENTITY column, you will need to:

• Create a new column
• Transfer the data from the existing IDENTITY column to the new column
• Drop the existing IDENTITY column.
• Rename the new column to the original column name

Answer 3

Bellow code working as fine, when we don't know identity column name.

and want to copy data into new temp table like Invoice_DELETED. and next time we using: insert into Invoice_DELETED select * from Invoice where ...

SELECT t1.*
INTO Invoice_DELETED
FROM Invoice t1
LEFT JOIN Invoice ON 1 = 0
--WHERE t1.InvoiceID = @InvoiceID

special thanks to 'Andriy M'

for more explanation see: https://dba.stackexchange.com/a/138345/101038

Answer 4

Following script removes Identity field for a column named 'Id'

Hope it helps.

BEGIN TRAN
BEGIN TRY
    EXEC sp_rename '[SomeTable].[Id]', 'OldId';

    ALTER TABLE [SomeTable] ADD Id int NULL

    EXEC ('UPDATE [SomeTable] SET Id = OldId')

    ALTER TABLE [SomeTable] NOCHECK CONSTRAINT ALL

    ALTER TABLE [SomeTable] DROP CONSTRAINT [PK_constraintName];
    ALTER TABLE [SomeTable] DROP COLUMN OldId
    ALTER TABLE [SomeTable] ALTER COLUMN [Id] INTEGER NOT NULL
    ALTER TABLE [SomeTable] ADD CONSTRAINT PK_JobInfo PRIMARY KEY (Id)

    ALTER TABLE [SomeTable] CHECK CONSTRAINT ALL

    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN   
    SELECT ERROR_MESSAGE ()
END CATCH

Answer 5

This gets messy with foreign and primary key constraints, so here's some scripts to help you on your way:

First, create a duplicate column with a temporary name:

alter table yourTable add tempId int NOT NULL default -1;
update yourTable set tempId = id;

Next, get the name of your primary key constraint:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'yourTable';

Now try drop the primary key constraint for your column:

ALTER TABLE yourTable DROP CONSTRAINT PK_yourTable_id;

If you have foreign keys, it will fail, so if so drop the foreign key constraints. KEEP TRACK OF WHICH TABLES YOU RUN THIS FOR SO YOU CAN ADD THE CONSTRAINTS BACK IN LATER!!!

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'otherTable';
alter table otherTable drop constraint fk_otherTable_yourTable;
commit;
..

Once all of your foreign key constraints have been removed, you'll be able to remove the PK constraint, drop that column, rename your temp column, and add the PK constraint to that column:

ALTER TABLE yourTable DROP CONSTRAINT PK_yourTable_id;
alter table yourTable drop column id;
EXEC sp_rename 'yourTable.tempId', 'id', 'COLUMN';
ALTER TABLE yourTable ADD CONSTRAINT PK_yourTable_id PRIMARY KEY (id) 
commit;

Finally, add the FK constraints back in:

alter table otherTable add constraint fk_otherTable_yourTable foreign key (yourTable_id) references yourTable(id);
..

El Fin!

Answer 6

Just for someone who have the same problem I did. If you just want to make some insert just once you can do something like this.

Lets suppose you have a table with two columns

ID Identity (1,1) | Name Varchar

and want to insert a row with the ID = 4. So you Reseed it to 3 so the next one is 4

DBCC CHECKIDENT([YourTable], RESEED, 3)

Make the Insert

INSERT  INTO [YourTable]
        ( Name )
VALUES  ( 'Client' )

And get your seed back to the highest ID, lets suppose is 15

DBCC CHECKIDENT([YourTable], RESEED, 15)

Done!