The Effects of Altering a Table

3 4

When you alter a table, any necessary changes to existing data rows will occur immediately upon execution of the ALTER TABLE T-SQL command or, if you're using Enterprise Manager, upon saving the changes. SQL Server puts a lock on the table so that no other users can access it while the changes are being made. A modification that requires a change in all rows of a large database, such as adding a NOT NULL column with a default value or dropping a column, might take some time and should be done with care, at a time when user access is at a minimum. When you alter a column by changing its data length, precision, or scale, the table is re-created in the database, and the existing data is converted to the new data type.

All table changes are logged and fully recoverable in the event that a system crash or fatal error occurs during the alteration process. Once the alteration is completed successfully, however, you have to restore from a backup to return the table to its original status. (Backing up and restoring a database are explained in Chapters 32 and 33.)