Remove Identity from a column in a table

how to remove identity constraint from table in sql server
remove identity from column sql server 2008 using script
how to remove identity in sql server using query
remove identity from column in sql server using query
disable identity column in sql server
sql server remove identity increment
how to update identity column in sql server
add identity to existing column in sql server

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?

How to remove identity on primary key column in SQL Server 2016 , There is no straightforward T-SQL like ALTER TABLE… or MODIFY COLUMN to add an Identity Property to an existing column in a table. How to remove IDENTITY from a Column of a SQL Server? The one line answer is – There is no specific way for disabling the IDENTITY on a Table Column. There are few hacky ways to do this so let me share here. Option 1: Create a replica blank table without any constraint; Transfer all data from your main table to replica table

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):

  1. Remove all foreign keys that point to this table
  2. Script the table to be created; rename everything e.g. 'MyTable2', 'MyIndex2', etc. Remove the IDENTITY specification.
  3. You should now have two "identical"-ish tables, one full, the other empty with no IDENTITY.
  4. Run ALTER TABLE [Original] SWITCH TO [Original2]
  5. 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).
  6. 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 SERVER - Add or Remove Identity Property on Column, Here is a simple table that has two columns and one column is the identity column. CREATE TABLE [dbo].[Test1]( [id] [int]  This section presents ways to change or drop an IDENTITY column from a table. The ALTER TABLE statement lets you add, remove, or alter a field in any table definition. Use the ALTER TABLE statement to modify an IDENTITY field.

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!

Add or drop identity property for an existing SQL Server column, I have a Table Which has a column that is set to identity by mistake and it is on my customers server, so I need to create my sql commands and  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.

How to drop identity property of Column - MSDN, An example. In AdventureWorks database, there's a master table called [Person].[​AddressType] that has only 6 Rows. All master tables  Since the identical schema restriction does not include the IDENTITY column property, SWITCH is a handy technique to add or remove IDENTITY from an existing column of a non-empty table. For an empty table, a simple drop and create is easier and more efficient. Remove IDENTITY from an Existing Column

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

remove Identity from a column – SQLServerCentral, The procedure in Listing 3 produces one gotcha: Regardless of the original IDENTITY column's ordinal position in the table, the new column appears as the  TRUNCATE TABLE [TestTable] GO When the TRUNCATE statement is executed it will remove all the rows. However, when a new record is inserted the identity value is increased from 11 (which is original value). TRUNCATE resets the identity value to the original seed value of the table.

Remove IDENTITY property from a primary key column in , Occasionally, these schema modifications require removing the IDENTITY property from an existing non-empty table while retaining the existing  Another option if we want the identity column to be applied to an existing column name is. Add a new column and set it as an identity. Remove the old column. Rename the new column to the old column name. In this way, we get the feeling that the identity has been applied to the existing column in the table. Script

Removing the IDENTITY Property from an Existing Column, what happen if more than one table is present with in database with foreign keys dependency. Lets take an example first, here we will create three tables tbCountry  How to Add or Remove Identity Property on Column from a table in SQL server September 26, 2017 Leave a Reply I got stuck at a point on removing the identity column from a table in a database, thinking much about T-SQL scripts to remove the identity property.

Add or Remove IDENTITY Property From an Existing Column , Reseed a SQL Server identity column? For instance, if you copy all the table's records to an archive table and then delete all the records in the source table, you might want to reseed the

Comments
  • Can you post the schema of the table here?
  • I'm sure there are excellent reasons that SQL Server doesn't support removing an identity property from a column via a simple ALTER TABLE ... statement, but it's nevertheless making me sad currently that that's the case.
  • you can remove an Identity spec. In fact I had to do it yesterday using SSMS although not on 500 million rows.
  • @simon if you script out your changes, you'll see SSMS is actually creating a copy of the table w/o the identity property.
  • I just want to add to rename the new column to the original column's name. Also, if this identity column is used as part of a foreign key in another table, you're going to have to drop the constraints first, then take action as @AdamWenger mentioned about removing the identity attribute/property ..You can also look into this link for more details about removing the attribute only: blog.sqlauthority.com/2009/05/03/… ..Good luck!
  • To the person who down-voted - I would appreciate hearing what you did not like about my answer.
  • Take a look at Mark Sowul's answer below. Obviates the need to move data from column to column. Using Mark's answer you are only shuffling metadata. No big deal unless you are working on a table that has tens or hundreds of millions of rows. Plus Mark's answer prevents the relocation of the column in the table schema. I just tried it and it worked like a charm. Very clever.
  • I don't have time to actually try this, but it's a good thing to know the next time I really need to drop an identity.
  • This should be the accepted answer. It is the only real way to remove an identity column without massive data migration.
  • Wow! This is very clever.
  • This answer is phenomenal! Thanks!
  • If using SQL Management Studio to script out the table, be sure to turn on Tools > Options > SQL Server Object Explorer > Scripting > Table and view options > Script indexes (False by default)
  • pay attention to your sql server version, I tried in azure sql server, not all operations here is supported in azure version sql.
  • Thanks for the answer! Helped me a lot! You just should add a verification for indexes that reference the column. Something like: Select t.name 'Table', i.name 'Index', c.name 'Column', i.is_primary_key, i.is_unique From sys.tables t Inner Join sys.indexes i On i.object_id = t.object_id Inner Join sys.index_columns ic ON ic.object_id = i.object_id And i.index_id = ic.index_id Inner Join sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id Where t.name = 'tableName' Order By t.name, i.name, i.index_id, ic.index_column_id