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);
ENDCREATE TABLE IF NOT EXISTS syntax from MySQL/PostgreSQL tutorials — it’s a syntax error in T-SQL. The OBJECT_ID(...) IS NULL check above is the standard SQL Server equivalent, and you’ll see it constantly in real migration scripts.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';sp_rename does not update any views, stored procedures, or application code referencing the old name — it only changes the object’s internal metadata name. Renaming a production table is a bigger operation than it looks.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 permanentEnjoyed this?
Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.
📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook
Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server Fundamentals, coming soon on this site.