Thursday, April 19, 2012

SQL Server Times Out When Adding a New Column to a Table

Recently at work, I needed to add an auto-incremented int field to a table that had 2.5 million records and was about 50 columns wide. Not a terribly large table to some folks, but our server is not the most robust. So every time I tried to do this in the UI in SSMS, it would process for a while and then time out and tell me something to the effect that the changes could not be save to the table. I then got the bright idea just to try this as a query:

ALTER TABLE [database].[dbo].[tableName]
ADD ID INT IDENTITY(1,1) NOT NULL

It processed in something like 2 second and worked like a charm. Weird that it should fail in the UI, but work as a script. I will keep this in mind. Also, it NEVER hurts to be able to write the T-SQL by hand to accomplish a task.

No comments:

Post a Comment