May 2018
Intermediate to advanced
576 pages
30h 25m
English
The ALTER TABLE statement, which is used to add or drop a column, takes a full table lock (at the AccessExclusiveLock lock level) so that it can prevent all other actions on the table. So we want it to be as fast as possible.
The DROP COLUMN command doesn't actually remove the column from each row of the table; it just marks the column as dropped. This makes DROP COLUMN a very fast operation.
The ADD COLUMN command is also very fast if we are adding a nullable column with a null default value. If we use a NOT NULL constraint or specify an explicit default value, then we'll need to rewrite every row of the table, which can be quite slow.
The ALTER TABLE command allows us to execute many column operations at once, as shown in ...