SQL SERVER – How to an Add Identity Column to Table in SQL Server
Posted
by Pinal Dave
on SQL Authority
See other posts from SQL Authority
or by Pinal Dave
Published on Sat, 27 Jul 2013 01:30:43 +0000
Indexed on
2013/08/02
15:47 UTC
Read the original article
Hit count: 484
SQL Server
|sql
|SQL Query
|SQL Tips and Tricks
|SQL Authority
|T SQL
|PostADay
|Identity
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
© SQL Authority or respective owner