February 28, 2011 at 4:30 pm
How to code to set identity to ID column?
I tried to set it in table design view but got time out error.
I set primary key for ID column successfully but need to set it "identity" on and increase by 1 using code.
February 28, 2011 at 4:52 pm
The basic syntax is like this:
CREATE TABLE x
(MyIdColumn int identity(1,1))
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2011 at 4:59 pm
But table x is already existing. If there is no data it is easier to set identity on in design view. Since there are about 100000 records in it, set identity to "yes" failed due to time out error.
February 28, 2011 at 5:38 pm
Yes, it will, because SSMS recreates the entire table with a limited timeout.
Make the change in the designer but instead of clicking save, generate the script and run it from a management studio window. The timeout is 0, so it will run until done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2011 at 5:39 pm
You're kind of stuck. There's no way to simply modify the column. You can recreate the table, which is what the GUI is trying to do for you, or you can use ALTER TABLE to drop the existing column and then re-add that column, again using ALTER TABLE.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 1, 2011 at 6:43 am
Thank you for help.
I used the way GilaMonster taught me and set identity on successfully.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply