Like many .Net Development teams we use SQL Server 2005 as our database. A colleague of mine was adding a column to a pretty hefty table the other day and was having problems. The operation was timing out in management studio. The reason for this problem is the way that management studio makes the changes you ask for.
As an example, see the table design below. It’s a trivial example, but the same goes for any table.
A simple table
The equivalent of what my colleague was doing was to add a new column and move it to be between ColumnA and ColumnB, because logically the piece of information that would be stored in the new field was related to ColumnA. The SQL that Management Studio was generating to perform this task went through the following steps:
1. Create a new table that is a copy of the original with the new column added.
2. Copy the data from the original table to the new table. (In my colleague’s case several million rows!)
3. Drop the original table.
4. Rename the new table to the original name.
This, understandably, was taking quite a while, particularly step 2.
When we added the new column at the end of the table, the SQL that Management Studio created was the equivalent of:
ALTER TABLE Example_T ADD ColumnC varchar(50) NOT NULL;
which executes in less than a second.
The lesson of this is to think very carefully before adding a new column into the middle of an existing table. If the column would be ok at the end, leave it there. If not, and your table is large, expect a wait and a very busy database server! Step 2 above locks the content in the original table too, which is likely to cause problems for your users if you’re working on a live system. SQL Management Studio provides a button on the toolbar which allows you to see the SQL script that will be used to perform your changes (The one that looks like a scroll of paper with a floppy disk beside it.) I strongly suggest taking advantage of that to check your changes are doing what you expect.
