SQL SERVER – How to an Add Identity Column to Table in SQL Server

Here is the question I received on SQLAuthority Fan Page.

“How do I add an identity column to Table in SQL Server? “

Sometime the questions are very very simple but the answer is not easy to find.

Scenario 1:

If you are table does not have identity column, you can simply add the identity column by executing following script:


Scenario 2:

If your table already has a column which you want to convert to identity column, you can’t do that directly. There is a workaround for the same which I have discussed in depth over the article Add or Remove Identity Property on Column.

Scenario 3:

If your table has already identity column and you can want to add another identity column for any reason – that is not possible. A table can have only one identity column. If you try to have multiple identity column your table, it will give following error.

Msg 2744, Level 16, State 2, Line 2
Multiple identity columns specified for table ‘MyTable‘. Only one identity column per table is allowed.

Leave a comment if you have any suggestion.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

6 thoughts on “SQL SERVER – How to an Add Identity Column to Table in SQL Server

  1. Pingback: SQLAuthority News – 5 days of SQL Server Reporting Service (SSRS) Summary | Journey to SQL Authority with Pinal Dave

  2. i) create another column as uniqueidentifier default newid()
    ii) Or populate another column manually(maintain sequence),by creating function which will fetch last value manipulate that value and return new value.
    Or sql server 2012 new feature Sequence can be use


  3. Even sequence in sql server won’t generate value like .1,.2,.3 etc.
    so i think you hv to define UDF to achieve so.either do it manually or make that column as computed .


  4. I use this to give a RowID to a table I imported using BULK INSERT. I then query my table for certain fields (i.e., Column1 like ‘____.____%’ to seek account numbers) but I get different values every time, even with the same file!

    Are there hidden traps to this technique? It is TOTALLY not working for me.

    See https://social.msdn.microsoft.com/Forums/sqlserver/en-US/acc59cef-258e-4f00-aada-15725e4816de/bulk-insert-yields-inconsistent-results?forum=sqldataaccess for the details.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s