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

User · Answer

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

User · Answer

ALTER TABLE TABLE NAME MODIFY  COLUMN NAME DROP IDENTITY

User · Answer

In SQL Server you can turn on and off identity insert like this  SET IDENTITY INSERT table name ON -- run your queries here SET IDENTITY INSERT table name OFF

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

ALTER TABLE tablename add newcolumn int update tablename set newcolumn existingcolumnname ALTER TABLE tablename DROP COLUMN existingcolumnname  EXEC sp RENAME  tablename oldcolumn     newcolumnname    COLUMN    However above code works only if no primary-foreign key relation

User · Answer

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

User · Answer

I had the same requirement  and you could try this way  which I personally recommend you  please manually design your table and generate the script  and what I did below was renaming the old table and also its constraint for backup      To prevent any potential data loss issues  you should review this script in detail before running it outside the context of the database designer    BEGIN TRANSACTION  SET QUOTED IDENTIFIER ON SET ARITHABORT ON SET NUMERIC ROUNDABORT OFF SET CONCAT NULL YIELDS NULL ON SET ANSI NULLS ON SET ANSI PADDING ON SET ANSI WARNINGS ON COMMIT BEGIN TRANSACTION GO ALTER TABLE dbo SI Provider Profile     DROP CONSTRAINT DF SI Provider Profile SIdtDateTimeStamp GO ALTER TABLE dbo SI Provider Profile     DROP CONSTRAINT DF SI Provider Profile SIbHotelPreLoaded GO CREATE TABLE dbo Tmp SI Provider Profile           SI lProvider Profile ID int NOT NULL      SI lSerko Integrator Token ID int NOT NULL      SI sSerko Integrator Provider varchar 50  NOT NULL      SI sSerko Integrator Profile varchar 50  NOT NULL      SI dtDate Time Stamp datetime NOT NULL      SI lProvider ID int NULL      SI sDisplay Name varchar 10  NULL      SI lPurchased From int NULL      SI sProvider UniqueID varchar 255  NULL      SI bHotel Pre Loaded bit NOT NULL      SI sSiteName varchar 255  NULL        ON  PRIMARY  GO ALTER TABLE dbo Tmp SI Provider Profile SET  LOCK ESCALATION   TABLE  GO ALTER TABLE dbo Tmp SI Provider Profile ADD CONSTRAINT     DF SI Provider Profile SIdtDateTimeStamp DEFAULT  getdate    FOR SI dtDate Time Stamp GO ALTER TABLE dbo Tmp SI Provider Profile ADD CONSTRAINT     DF SI Provider Profile SIbHotelPreLoaded DEFAULT   0   FOR SI bHotel Pre Loaded GO IF EXISTS SELECT   FROM dbo SI Provider Profile          EXEC  INSERT INTO dbo Tmp SI Provider Profile  SI lProvider Profile ID  SI lSerko Integrator Token ID  SI sSerko Integrator Provider  SI sSerko Integrator Profile  SI dtDate Time Stamp  SI lProvider ID  SI sDisplay Name  SI lPurchased From  SI sProvider UniqueID  SI bHotel Pre Loaded  SI sSiteName          SELECT SI lProvider Profile ID  SI lSerko Integrator Token ID  SI sSerko Integrator Provider  SI sSerko Integrator Profile  SI dtDate Time Stamp  SI lProvider ID  SI sDisplay Name  SI lPurchased From  SI sProvider UniqueID  SI bHotel Pre Loaded  SI sSiteName FROM dbo SI Provider Profile WITH  HOLDLOCK TABLOCKX    GO  -- Rename the primary key constraint or unique key In SQL Server constraints such as primary keys or foreign keys are objects in their own right  even though they are dependent upon the  containing  table  EXEC sp rename  dbo SI Provider Profile PK SI Provider Profile    PK SI Provider Profile Old   GO -- backup old table in case of  EXECUTE sp rename N dbo SI Provider Profile   N SI Provider Profile Old    OBJECT  GO  EXECUTE sp rename N dbo Tmp SI Provider Profile   N SI Provider Profile    OBJECT  GO  ALTER TABLE dbo SI Provider Profile ADD CONSTRAINT     PK SI Provider Profile PRIMARY KEY NONCLUSTERED            SI lProvider Profile ID       WITH  PAD INDEX   OFF  FILLFACTOR   90  STATISTICS NORECOMPUTE   OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS   ON  ALLOW PAGE LOCKS   ON  ON  PRIMARY   GO COMMIT TRANSACTION

User · Answer

Bellow code working as fine  when we don t know identity column name   Need 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   For more explanation see   https   dba stackexchange com a 138345 101038

User · Answer

If you want to do this without adding and populating a new column  without reordering the columns  and with almost no downtime because no data is changing on the table  let s do some magic with partitioning functionality  but since no partitions are used you don t need Enterprise edition     Remove all foreign keys that point to this table Script the table to be created  rename everything e g   MyTable2    MyIndex2   etc  Remove the IDENTITY specification  You should now have two  identical -ish tables  one full  the other empty with no IDENTITY  Run ALTER TABLE  Original  SWITCH TO  Original2  Now your original table will be empty and the new one will have the data   You have switched the metadata for the two tables  instant    Drop the original  now-empty table   exec sys sp rename to rename the various schema objects back to the original names  and then you can recreate your foreign keys    For example  given   CREATE TABLE Original     Id INT IDENTITY PRIMARY KEY   Value NVARCHAR 300     CREATE NONCLUSTERED INDEX IX Original Value ON Original  Value    INSERT INTO Original SELECT  abcd  UNION ALL  SELECT  defg     You can do the following   --create new table with no IDENTITY CREATE TABLE Original2     Id INT PRIMARY KEY   Value NVARCHAR 300     CREATE NONCLUSTERED INDEX IX Original Value2 ON Original2  Value    --data before switch SELECT  Original     FROM Original UNION ALL SELECT  Original2     FROM Original2   ALTER TABLE Original SWITCH TO Original2   --data after switch SELECT  Original     FROM Original UNION ALL SELECT  Original2     FROM Original2   --clean up  IF NOT EXISTS  SELECT   FROM Original  DROP TABLE Original  EXEC sys sp rename  Original2 IX Original Value2    IX Original Value    INDEX   EXEC sys sp rename  Original2    Original    OBJECT     UPDATE Original SET Id   Id   1   SELECT   FROM Original

[sql] Remove Identity from a column in a table

Examples related to sql

Examples related to sql-server

Examples related to tsql