Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Remove Identity from a column in a table

People also ask

Can we remove identity column SQL Server?

By design there is no simple way to turn on or turn off the identity feature for an existing column. The only clean way to do this is to create a new column and make it an identity column or create a new table and migrate your data.

How do I disable identity column in SQL?

To remove the identity from the column entirely is harder. The question covers it, but the basic idea is that you have to create a new column, copy the data over, then remove the identity column. Show activity on this post. The session that sets SET IDENTITY_INSERT is allowed to enter explicit values.

How do you alter a column with identity?

You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.


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

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;

For the record, as this has become increasingly popular, I have wanted to track down the original source (I didn't think of it myself), but of course I've long forgotten where I found the original idea. It may have been here; this was the only one I could find predating this answer (time flies, boys and girls): https://social.technet.microsoft.com/wiki/contents/articles/17738.sql-server-quick-way-to-remove-the-identity-property.aspx


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!


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';