CREATE, ALTER, DROP: SQL Server DDL Explained With Examples

DDL (Data Definition Language) statements define the structure of your database — tables, columns, and their types. Every DDL statement in SQL Server takes effect immediately and, unusually among major databases, is fully transactional — you can wrap DDL in BEGIN TRAN/ROLLBACK and undo it, which most other RDBMSes don’t allow.

CREATE: Build a New Table

CREATE TABLE dbo.Employee (
    employee_id   INT IDENTITY(1,1) PRIMARY KEY,
    first_name    NVARCHAR(50)  NOT NULL,
    last_name     NVARCHAR(50)  NOT NULL,
    hire_date     DATE          NOT NULL DEFAULT GETDATE(),
    salary        DECIMAL(10,2) NOT NULL
);

Notice the dbo. prefix — that’s the schema name. dbo (database owner) is the default schema every database starts with; schemas are namespaces that let you group related tables (e.g. sales.Order vs hr.Employee) and manage permissions per group. Always schema-qualify your table names in real code — unqualified names resolve against whatever the current user’s default schema happens to be, which is a subtle source of “works on my machine” bugs.

Guarding CREATE Against Re-Running a Script

CREATE TABLE IF NOT EXISTS dbo.Scratch_Test (id INT); -- NOT valid T-SQL!

-- The actual SQL Server idiom:
IF OBJECT_ID('dbo.Scratch_Test', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.Scratch_Test (id INT);
END

ALTER: Change an Existing Table

ALTER TABLE dbo.Employee ADD email NVARCHAR(100) NULL;
ALTER TABLE dbo.Employee ALTER COLUMN salary DECIMAL(12,2) NOT NULL;
ALTER TABLE dbo.Employee DROP COLUMN email;

Two things about ALTER COLUMN that surprise beginners: first, widening a type (DECIMAL(10,2) → DECIMAL(12,2), or VARCHAR(50) → VARCHAR(100)) is safe and fast. Narrowing one — or changing NULL to NOT NULL on a column that already has NULL values — fails outright if existing data can’t fit the new definition. SQL Server checks every existing row before allowing the change.

-- This fails if any existing row has a NULL email:
ALTER TABLE dbo.Employee ALTER COLUMN email NVARCHAR(100) NOT NULL;
-- Msg 515: Cannot insert the value NULL into column 'email' ...

-- The real-world fix: clean the data first, then tighten the constraint
UPDATE dbo.Employee SET email = 'unknown@example.com' WHERE email IS NULL;
ALTER TABLE dbo.Employee ALTER COLUMN email NVARCHAR(100) NOT NULL;

Renaming Things (It’s Not ALTER)

Unlike some databases, T-SQL doesn’t rename objects through ALTER — it uses a dedicated system procedure:

EXEC sp_rename 'dbo.Employee.email', 'contact_email', 'COLUMN';
EXEC sp_rename 'dbo.Employee', 'Staff';

DROP: Permanently Remove an Object

DROP TABLE IF EXISTS dbo.Scratch_Test;

Unlike CREATE TABLE IF NOT EXISTS, DROP TABLE IF EXISTS genuinely is valid modern T-SQL (SQL Server 2016+) — the asymmetry is just a quirk of which syntax Microsoft added and when.

The Danger Zone, Visualized

DROP TABLE deletes the table and every row in it, permanently, with no confirmation prompt. Always double-check you’re connected to the right database (SELECT DB_NAME();) before running DROP anywhere near a real environment.

DDL Is Transactional — Use It

Because DDL participates in transactions in SQL Server, you can test a risky structural change safely:

BEGIN TRAN;

ALTER TABLE dbo.Employee DROP COLUMN salary;
SELECT * FROM dbo.Employee; -- confirm it looks right

ROLLBACK; -- changed your mind — salary column is back, nothing happened
-- or: COMMIT; -- to make it permanent