SQL SERVER – How to an Add Identity Column to Table in SQL Server
- by Pinal Dave
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:
ALTER TABLE MyTable
ADD ID INT IDENTITY(1,1) NOT NULL
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)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL Tagged: Identity
