Here is the question I received on SQLAuthority Fan Page. They wanted to know how to add identity column to an existing table. The answer is to add a new column using the IDENTITY property.
“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.
Checks Before Using the IDENTITY Property on a Large Table
The script in Scenario 1 is short, but on a large table it does real work. SQL Server has to write a new value into every existing row, so the table stays locked while it runs, every row change is logged, and the log file can grow a lot. Run it in a quiet window and make sure there is enough log space first.
Also know that you cannot choose the order in which the existing rows get their numbers. If the order matters, for example by date, create a new table with the identity column, insert the rows with an ORDER BY, and then swap the tables.
Pick the data type with care. INT stops at a little over 2.1 billion, so use BIGINT for tables that will grow fast. After the change, check the current value with IDENT_CURRENT('MyTable'), and use SET IDENTITY_INSERT only when you really need to load your own values.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
10 Comments. Leave new
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
how to add decimal values in identity like .1;.2;.3 etc
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 .
Oh! Thanks.
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 for the details.
Hey Buddy! I just want to give you a shout out for all your hard work here on line. I’ve used your tips for years and want to thank you for your clear, concise examples. All the Best!
Can we Create Value like (ID varchar IDENTITY(A1000,1)).kindly Anyone reply
How to alter a table with huge data approx. 2 Billions rows with Identity column without downtime or minimal impact.
thanks, man. it’s great to be able to find quick answers to mssql questions. you’ve helped me more than once
How can you use scenario 1 but name the PK so it’s not some random system-generated name?!