The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

ALTER TABLE: change a structure that already contains data

The bases evolve: a new piece of information appears, a length changes, or we need to protect a rule that previously only lived in the application.

ALTER TABLE modifies the existing structure. The difficulty is not only in the syntax, but in checking what data, dependencies and concurrent operations will be affected.

Add a column

ALTER TABLE dbo.ProductoFavorito
ADD Comentario NVARCHAR(250) NULL;

Allowing NULL, the marker for an absent or unknown value, makes it possible to add the column even when existing rows do not yet have a comment.

If a new column is to be required, we need to decide what value the previous rows will receive:

ALTER TABLE dbo.ProductoFavorito
ADD Activo BIT NOT NULL
    CONSTRAINT DF_ProductoFavorito_Activo DEFAULT 1
    WITH VALUES;

WITH VALUES applies the default value to existing rows in this scenario.

Change a column

ALTER TABLE dbo.ProductoFavorito
ALTER COLUMN Comentario NVARCHAR(500) NULL;

Increasing a length is usually easier than reducing it. Before reducing it, we must locate values that would no longer fit and review indexes—structures that organize values to make rows easier to access—or dependent objects.

Add a restriction

The following condition combines two text functions. TRIM removes the trailing spaces and LEN counts the remaining characters. Thus, a string made up only of spaces is zero length.

ALTER TABLE dbo.ProductoFavorito
ADD CONSTRAINT CK_ProductoFavorito_Comentario
CHECK (Comentario IS NULL OR LEN(TRIM(Comentario)) > 0);

A constraint is a rule enforced by the table. SQL Server validates existing rows when adding one this way. If it finds incompatible data, the change fails, and we should correct it first instead of disabling constraint trust without understanding the consequences.

Delete a column or constraint

ALTER TABLE dbo.ProductoFavorito
DROP CONSTRAINT CK_ProductoFavorito_Comentario;

ALTER TABLE dbo.ProductoFavorito
DROP COLUMN Comentario;

Order matters: a constraint, index, or view can depend on the column and prevent its deletion.

Changes on large tables

Some changes require scanning or rewriting much of the table, retain schema locks—protections over the object’s definition—and consume transaction-log space, where SQL Server records changes, or additional storage. A short statement can become a long operation.

That’s why we test with a representative volume, measure duration and plan compatibility between the old and new versions of the application.

What should be checked

We inventory procedures, views, functions, indexes, keys, loads, and applications that use the column. We also check backups, the deployment window, rollback—the intended way to return to the preceding state—and behavior during concurrent executions.

Renaming a column does not safely update all of its consumers. Sometimes it is convenient to introduce the new structure, migrate consumers, and retire the old one in a later version.