SQL SERVER – Add Auto Incremental Identity Column to Table After Creating Table

Question: Is it possible to add an auto incremental identity column to any table in SQL Server after creating a table.

Answer: There are two answers – No and Yes. Let us see them one by one.

Answer No – If you have an integer column in your table and you want to convert that column to identity table. It is not possible with the help of SQL Server. You will just have to add a new column.

Answer Yes – If you want to add new column to the table, it is totally possible to do so with the help of following a script.

ALTER TABLE YourTable ADD IDCol INT IDENTITY(1,1)


If you want to convert your old column to int column, may be you can drop that first and add a new column identity right after that with the help of following a script.

ALTER TABLE YourTable DROP COLUMN IDCol
ALTER TABLE YourTable ADD IDCol INT IDENTITY(1,1)

Let me know if you have any other work around besides SSMS (as that option just drops table and recreates it).

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Identity, SQL Scripts
Previous Post
SQL SERVER – Row Offset in SQL Server For Different Version
Next Post
SQL SERVER – SQL Server 2008 Service Pack 4 – Download

Related Posts

14 Comments. Leave new

  • This can be done either by re-creating the table with the existing int column as identity + insert the data there and rename it to replace the original table, or use bcp out + truncate the table + change the field to identity + bcp in the data. Both of these require to use identity insert on.

    Reply
  • ALTER TABLE YourTable ADD IDCol2 INT IDENTITY(1,1)
    SET IDENTITY_INSERT YourTable ON
    UPDATE YourTable SET IDCol2 = IDCol
    SET IDENTITY_INSERT YourTable OFF
    ALTER TABLE YourTable DROP COLUMN IDCol
    EXEC sp_RENAME ‘YourTable.IDCol2’, ‘IDCol’, ‘COLUMN’

    — To replace the values you had —

    Reply
    • Thanks Robb…much better and complete solution. Keep in mind however that if the column being dropped has default values or indexes attached to it, they need to be dropped first and recreated for the new column

      Reply
  • nakulvachhrajani
    October 15, 2014 1:39 am

    I have written about adding IDENTITY columns to existing tables in 2 parts here: Part01 [http://beyondrelational.com/modules/2/blogs/77/posts/19516/0272-sql-server-identity-columns-myths-identity-columns-cannot-be-added-to-existing-tables-part-01.aspx] and Part 02 [http://beyondrelational.com/modules/2/blogs/77/posts/19518/0273-sql-server-identity-columns-myths-identity-columns-cannot-be-added-to-existing-tables-part-02.aspx]

    Reply
  • Great Pinal Love your posts

    Can we have auto increment when we have data already in tables ?

    Reply
  • How to auto-increment something like: 001,0004,0009,0011,0018…!!!!?

    Reply
  • if an auto incremented columns limit has exceed. what we have to do? because it will error, when you trying to insert new row.

    Reply
  • IN MYSQL(PHPmyAdmin) ,goto Struture menu and checkbox column from right and then Click on change from bottom buttons .You will be redirected with changing options.Change the column accordingly.

    Reply
  • how to set auto increment to column which is int datatype and primary key constraint

    Reply

Leave a Reply