Changing tables safely: ALTER and DROP

Tables change over a system’s life: a new column here, a longer name there. ALTER TABLE changes a table in place, keeping its data. Some changes are harmless, some are refused, and a few quietly destroy data. Try them on BuildMart’s products:

1ALTER TABLE dbo.Products ADD Barcode varchar(13) NULL;2GO3SELECT TOP (3) * FROM dbo.Products ORDER BY ProductId;

Adding columns

A new column that allows NULL is added instantly, with NULL in every existing row. A NOT NULL column needs a DEFAULT so the existing rows have a value:

1ALTER TABLE dbo.Customers ADD Newsletter bit NOT NULL2    CONSTRAINT DF_Customers_Newsletter DEFAULT 0;3GO4SELECT TOP (3) CustomerId, FirstName, Newsletter FROM dbo.Customers;

CustomerId  FirstName  Newsletter
----------  ---------  ----------
         1  Asha       0
         2  Ben        0
         3  Chloe      0

Changing a column

ALTER COLUMN changes a column’s type, length or NULL rule. Widening (nvarchar(60) to nvarchar(100), int to bigint) is always safe. Narrowing is checked against the data and refused if anything would be cut. Changing to a different kind of type converts every value with the normal conversion rules, and those can lose data without an error, as the decimal-to-int example in the widget shows.

Renaming

Renaming uses the system procedure sp_rename, not ALTER TABLE. SQL Server warns you, because every query, view and procedure that uses the old name breaks:

1EXEC sp_rename 'dbo.Customers.Phone', 'PhoneNumber', 'COLUMN';2GO3SELECT TOP (2) CustomerId, FirstName, PhoneNumber FROM dbo.Customers;

Caution: Changing any part of an object name could break scripts and stored procedures.

CustomerId  FirstName  PhoneNumber
----------  ---------  -------------
         1  Asha       0113 496 0001
         2  Ben        01904 555 010

Dropping columns, constraints and tables

ALTER TABLE … DROP COLUMN removes a column and its data. A column that a constraint uses cannot be dropped until the constraint is, which is where named constraints pay off: you can drop them by name. Tables are dropped with DROP TABLE, children before parents, because a table referenced by a foreign key cannot be dropped:

1ALTER TABLE dbo.Customers ADD Region nvarchar(20) NULL2    CONSTRAINT DF_Customers_Region DEFAULT N'North';3GO4ALTER TABLE dbo.Customers DROP COLUMN Region;   -- fails: the default uses it5GO6ALTER TABLE dbo.Customers DROP CONSTRAINT DF_Customers_Region;7ALTER TABLE dbo.Customers DROP COLUMN Region;8SELECT COUNT(*) AS RegionColumns FROM sys.columns9WHERE object_id = OBJECT_ID('dbo.Customers') AND name = 'Region';

Msg 5074, Level 16, State 1, Line 1
The object 'DF_Customers_Region' is dependent on column 'Region'.

Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN Region failed because one or more objects access this column.

RegionColumns
-------------
            0

Schema changes you can undo

In SQL Server, CREATE, ALTER and DROP take part in transactions like any INSERT or UPDATE. Wrap a risky change in a transaction, look at the result, and roll it back if it is wrong, data included:

1BEGIN TRANSACTION;2 3ALTER TABLE dbo.Products DROP COLUMN Unit;4SELECT COUNT(*) AS UnitColumns FROM sys.columns5WHERE object_id = OBJECT_ID('dbo.Products') AND name = 'Unit';6 7ROLLBACK;8 9SELECT TOP (2) Name, Unit FROM dbo.Products ORDER BY ProductId;

UnitColumns
-----------
          0

Name            Unit
--------------  ----
Claw hammer     each
Cordless drill  each

For changes to a live system, the professional routine is: script the change, run it on a copy of the database first, take a backup, run it in a transaction, check, then commit. Tools such as SQL Server Data Tools and migration frameworks (EF Core migrations, DbUp, Flyway) turn those scripts into versioned, repeatable steps.

1ALTER TABLE dbo.Products ADD Barcode varchar(13) NULL;2GO3UPDATE dbo.Products SET Barcode = '5012345678900' WHERE ProductId = 1;4 5CREATE UNIQUE INDEX UX_Products_Barcode ON dbo.Products (Barcode) WHERE Barcode IS NOT NULL;6 7SELECT TOP (2) ProductId, Name, Barcode FROM dbo.Products ORDER BY ProductId;

ProductId  Name            Barcode
---------  --------------  -------------
        1  Claw hammer     5012345678900
        2  Cordless drill  NULL