SQL Alter Table to Add a Column, does it do anything "scary"?

Question

I need to add a column to an existing table in my live sql database. I know how to use the ALTER command, but what I want to know is the effect this has on the table.

For example,, if using SQL Management Studio, it claims that adding a column will "drop and recreate" the table.

Will the ALTER table command do that as well?

This table is CONSTANTLY accessed and VERY important so I want to make VERY sure of this before going forward.

Answer 1

(Disclaimer: answer based on general SQL experience, not specific to MS SQL Server)

Try it out first, in a less critical database.

At the very least, it's likely going to require a table lock, which will block access for the duration of the change. That might a few milliseconds to a minute or more depending on the size of the table and the server load.

An ALTER command that only adds a column shouldn't remove any existing information.

Answer 2

No, ALTER TABLE x ADD y will not recreate the table, it will just add the column. The only problem you can have is if the column should be NOT NULL. In that case, the command will end with error unless you specify a default value (if there are already rows in that table). It's because it needs to know what to fill to the existing rows.

Answer 3

If he do anything "scary", this may be only bug in your database server code.

But you must know, the database was locked on few time depends on database lenght, while column was adding.

Answer 4

Have a look at the script it generates.

If you tried to insert a column in the GUI, this will rewrite the table.

Adding a column at the end will not.

Answer 5

By "constantly accessed" do you mean "I don't have any downtime or maintenance window in which to make critical changes to the database"? If so, then that is the scary part. Like everyone says, this update will require an exclusive lock the table, and if it has to wait for that lock, then you may get blocking, timeouts, and possible irate users.

Too, the new column may require updating the table. I'm not 100% clear on what happens here, but if (this is a "what if" example, but it could happen) you add a char(100) column to a 10,000-row table that had just had its clustered index rebuilt with fillfactor = 0, then you are adding 100 bytes per row to pages that are already full, and where does that data go? Either you get page splits or forwarded records, both of which are going to take time for SQL to build -- meaning long blocks and further delays to access requests to the table.

Seriously, try to find downtime, or at least low-volume time (Sunday 2am?) to do work like this. And test it if you can... but very few places have testing sites that emulate seriously busy Production websites. If nothing else, a test on a copy of you DB will show roughly how long it will take, if you can get a downtime window.

Answer 6

Depends on how your database engine lays out files. "Scary" behavior can be avoided if either:

1. It's possible to add a NULL to the end of each row without actually adding bytes to the row, or
2. Each row has sufficient room for expansion.

In SQLite, for example, ADD COLUMN is always an O(1) operation.

MS SQL Server does not have property (1), and may require page splits if property (2) is false.