Narcis Miclaus

10 min read

SQL Server, from the ground up Lesson 5 / 40

CREATE, ALTER, DROP: making and breaking tables

DDL basics, IF EXISTS patterns, idempotent scripts, and the 'oh no I dropped prod' drill. Plus how to change a column without breaking the app.

Every database starts as a blinking cursor and a CREATE TABLE statement. Every database ends as a long series of ALTER TABLE statements written by people who didn’t plan for their schema to change. And once in a while, somebody runs DROP TABLE in the wrong query window and the day becomes about resumes.

This lesson is about Data Definition Language — DDL — the subset of SQL that creates, modifies, and destroys database objects. It’s the first SQL most people learn and the part where the most catastrophic mistakes happen. By the end you’ll know the safe patterns, the unsafe ones, and what to do if you’ve already made the unsafe choice.

The three big ones

  • CREATE — makes a new object (table, index, view, procedure, schema, database).
  • ALTER — changes an existing object.
  • DROP — deletes an object.

There’s also TRUNCATE TABLE, which deletes all the rows but keeps the table. It’s much faster than DELETE because it’s a metadata operation that just resets the data pages, and it doesn’t log each row individually. More on that in a minute.

Every DDL command in SQL Server runs inside a transaction, just like DML. That means you can BEGIN TRAN; DROP TABLE ...; ROLLBACK; and get your table back. More than once, this single feature has saved my career. Learn to start every ad-hoc DDL session with BEGIN TRAN and to end it with either COMMIT or ROLLBACK. Lesson 18 will cover transactions in detail. For now, know that DDL is transactional in SQL Server and use it.

Creating tables, the practical version

The lesson-3 example was intentionally minimal. Here’s a real-world CREATE TABLE — the kind Runehold’s backend team would actually ship to production for storing sales orders:

USE Runehold;
GO

CREATE TABLE Sales.Orders (
    OrderId         BIGINT        IDENTITY(1,1) NOT NULL,
    CustomerId      INT                         NOT NULL,
    OrderDate       DATETIME2(0)                NOT NULL DEFAULT SYSUTCDATETIME(),
    Status          TINYINT                     NOT NULL DEFAULT 0,
    Total           DECIMAL(19, 4)              NOT NULL,   -- amounts in EUR, 4 decimals for exact math
    Currency        CHAR(3)                     NOT NULL DEFAULT 'EUR',
    CountryCode     CHAR(2)                     NOT NULL,   -- NL, DE, IT, etc.
    VatRate         DECIMAL(5, 4)               NOT NULL,   -- 0.2100 for NL 21%
    Notes           NVARCHAR(4000)                  NULL,
    CreatedAt       DATETIME2(0)                NOT NULL DEFAULT SYSUTCDATETIME(),
    CreatedBy       NVARCHAR(128)               NOT NULL DEFAULT SUSER_SNAME(),
    UpdatedAt       DATETIME2(0)                NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedBy       NVARCHAR(128)               NOT NULL DEFAULT SUSER_SNAME(),
    RowVersion      ROWVERSION,
    CONSTRAINT pk_Orders        PRIMARY KEY CLUSTERED (OrderId),
    CONSTRAINT fk_Orders_Customer FOREIGN KEY (CustomerId)
        REFERENCES Sales.Customer (CustomerId),
    CONSTRAINT ck_Orders_Total_NonNegative CHECK (Total >= 0),
    CONSTRAINT ck_Orders_Vat_Sensible       CHECK (VatRate BETWEEN 0 AND 0.3)
);

(I called the table Orders — plural — on purpose. Order is a T-SQL reserved word, so if you use it as a table name you’d have to quote it with [Order] every single time. Pluralising it saves you from that paper-cut forever. Naming conventions are a personal war; pick a side and stop litigating.)

Pattern notes, because every line is doing work:

  • BIGINT IDENTITY(1,1) primary key. BIGINT because orders-like tables grow fast. IDENTITY(1,1) for auto-increment. Clustered primary key because that’s the default and we want it.
  • NOT NULL by default. Only one column (Notes) is nullable, and it’s explicit. Every other column has to have a value. This one habit prevents more bugs than any other.
  • Audit columns. CreatedAt, CreatedBy, UpdatedAt, UpdatedBy. If you aren’t tracking who inserted and who last updated the row, you’ll want them the first time something weird happens in production and nobody knows why.
  • ROWVERSION — an 8-byte value that auto-updates on every change. Perfect for optimistic concurrency (“has someone else modified this row since I read it?”) and for change-tracking sync scenarios.
  • Named constraints. pk_, fk_, ck_. When SQL Server auto-names constraints, you get names like PK__Orders__6A50D8DAF4D8EFDE that are different on every environment. Named constraints keep deploys repeatable.
  • CHECK constraint. Pushes a business rule (total can’t be negative) down into the schema. One line; will catch bugs for the rest of the table’s life.
  • FOREIGN KEY. Data integrity, referential integrity, the reason relational databases are not just “files with an index.”

There’s more you can add (FILLFACTOR, FILESTREAM, partitioning, temporal tables, computed columns) but the above is the workhorse shape.

Idempotent create: IF NOT EXISTS

You’ll often want to write a script that can be run multiple times without blowing up. The pattern for a table:

IF OBJECT_ID(N'Sales.Orders', N'U') IS NULL
BEGIN
    CREATE TABLE Sales.Orders (
        OrderId BIGINT IDENTITY(1,1) NOT NULL,
        -- ... rest of columns ...
        CONSTRAINT pk_Orders PRIMARY KEY CLUSTERED (OrderId)
    );
END;

OBJECT_ID('name', 'type') returns the ID if the object exists, NULL if it doesn’t. The type parameter 'U' means “user table.” Other common values: 'V' (view), 'P' (procedure), 'FN' (scalar function), 'TR' (trigger).

SQL Server 2016+ shorthand:

CREATE TABLE IF NOT EXISTS Sales.Orders (...);  -- 2022+
DROP TABLE IF EXISTS Sales.Orders;              -- 2016+

Use IF EXISTS / IF NOT EXISTS wherever you can. Migration scripts that crash on a re-run are a bad habit to pick up.

Altering tables

Three flavours of ALTER that cover 90% of your needs:

Adding a column

ALTER TABLE Sales.Orders
ADD PromotionCode NVARCHAR(40) NULL;

Adding a NULL-able column is a metadata-only operation. It’s instant even on a billion-row table — SQL Server just updates the schema; existing rows get the column “filled” with NULL virtually. No data is rewritten.

Adding a NOT NULL column with a DEFAULT is also metadata-only since SQL Server 2012, as long as the default is a constant (not a function call). This is called the “online add column” feature and it’s great.

Adding a NOT NULL column without a default fails, because SQL Server can’t guess what to put in existing rows. The classic workaround: add it as nullable, backfill with an UPDATE, then ALTER it to NOT NULL once every row has a value.

Modifying a column’s type

ALTER TABLE Sales.Orders
ALTER COLUMN Notes NVARCHAR(MAX) NULL;

Widening a column (NVARCHAR(100) → NVARCHAR(200)) is typically fast — metadata change. Narrowing (NVARCHAR(200) → NVARCHAR(100)) requires scanning every row to make sure they fit, and rewriting any that do. On a big table this is not instant. Plan a maintenance window or use online index rebuilds for tables with a lot of traffic.

Changing type entirely (INT → BIGINT) also rewrites every row. Again, big tables = big downtime = plan carefully.

Dropping a column

ALTER TABLE Sales.Orders
DROP COLUMN PromotionCode;

Metadata-only. Instant. But the actual bytes aren’t reclaimed until the next index rebuild, so the table’s disk footprint doesn’t shrink immediately. On a hot table where you need to reclaim space, schedule an ALTER INDEX ... REBUILD after the drop.

Constraints, renames, and the fiddly bits

-- Drop a constraint
ALTER TABLE Sales.Orders DROP CONSTRAINT ck_Orders_Total_NonNegative;

-- Add a constraint
ALTER TABLE Sales.Orders
ADD CONSTRAINT ck_Orders_Total_NonNegative CHECK (Total >= 0);

-- Rename a column
EXEC sp_rename 'Sales.Orders.Notes', 'CustomerNotes', 'COLUMN';

-- Rename a table
EXEC sp_rename 'Sales.Orders', 'OrderHeader';

sp_rename is the weirdly-named but official way to rename things. Note: renaming breaks every stored procedure and view that references the old name. SQL Server doesn’t follow dependencies. You have to hunt them down yourself, usually with a sys.sql_modules query (see below).

Dropping things

DROP TABLE Sales.Orders;
DROP INDEX IX_OrderDate ON Sales.Orders;
DROP VIEW Sales.v_ActiveOrders;
DROP PROCEDURE Sales.usp_GetOrder;
DROP DATABASE OldDatabase;
DROP SCHEMA Legacy;

Dropping a table with foreign-key references into it will fail. You must drop the foreign keys first, or drop the child tables first. This saves you from dropping referenced tables by accident, which is why the error message is arguably a feature.

Dropping a schema that still contains objects will fail. Move or drop the objects first, then drop the schema.

Dropping an index is usually safe. Dropping a clustered index converts the table to a heap, which we covered in lesson 21 as “almost always a mistake.” So: don’t drop clustered indexes unless you’re immediately adding another one.

The “oh no I dropped it” drill

I am going to assume you’ve done everything right and written this from memory when the moment comes:

Step 1. Panic quietly

Do not CREATE TABLE to put it back. That creates a new empty table with the same name. Your data is still gone from its original location but now you’ve made a new empty tombstone.

Step 2. Was it inside a transaction?

If your SSMS window had BEGIN TRAN at the top, immediately run ROLLBACK. If the transaction is still open, you get everything back. Check with:

SELECT @@TRANCOUNT;

If this returns > 0, you have an open transaction. ROLLBACK TRAN; undoes the drop. You just got a second chance.

If @@TRANCOUNT is 0, the transaction committed (or there was no transaction). You’re on to step 3.

Step 3. Restore from backup

This is where your backup strategy earns its keep. If you do daily full backups with transaction log backups every 15 minutes (standard for any serious OLTP system), you can restore to a point-in-time just before the drop. That restore goes into a temporary database, from which you copy the missing table into production.

RESTORE DATABASE MyApp_Recovery
FROM DISK = N'C:\Backups\MyApp_2025-12-10.bak'
WITH MOVE ...

RESTORE LOG MyApp_Recovery
FROM DISK = N'C:\Backups\MyApp_log_2025-12-10_1415.trn'
WITH STOPAT = '2025-12-10 14:29:59', NORECOVERY, ...

-- Continue applying logs up to your stopat time.
RESTORE LOG MyApp_Recovery
FROM DISK = N'C:\Backups\MyApp_log_2025-12-10_1430.trn'
WITH STOPAT = '2025-12-10 14:29:59', RECOVERY;

-- Now pull the table back
SELECT * INTO Sales.Orders_Recovered
FROM MyApp_Recovery.Sales.Orders;

This is why lesson 31 (backups) is the most important lesson in the DBA module. A schedule of daily full + hourly log backups means any drop-related mistake is at most one hour of data loss. Without that schedule, you might be explaining to your board why you lost a customer’s quarter of transactions.

Step 4. Tell everyone

Don’t hide it. “I dropped the wrong thing, here’s the recovery plan, here’s the ETA” is the right message. Trying to hide a data loss never ends well.

Step 5. The post-mortem

After it’s fixed, block time in a calendar and write down what happened. The single most preventable class of “dropped the wrong thing” is running scripts in the wrong environment. Use SSMS colour-coded connections (Options → Connection Colors → Red for prod). Use a prompt in Azure Data Studio that screams “YOU ARE IN PRODUCTION.” Never, ever have the same connection open to dev and prod at the same time. Every DBA has one of these stories. The trick is stopping at one.

TRUNCATE TABLE vs DELETE

Quick comparison:

-- DELETE: logs each row, slower, can be filtered
DELETE FROM Sales.Orders WHERE Status = 9;

-- TRUNCATE: resets all pages, fast, no filter
TRUNCATE TABLE Sales.Orders;

TRUNCATE is a DDL command, not DML. It’s near-instant on any size of table because it’s really just marking the pages as empty. Three gotchas:

  1. No filter. TRUNCATE TABLE X WHERE condition; doesn’t exist. You either nuke the whole table or you don’t.
  2. No foreign key pointing at it. If another table references this one with a FK, TRUNCATE fails. Microsoft assumes you’d rather know about the FK than silently break referential integrity.
  3. Identity resets. TRUNCATE resets the IDENTITY seed back to the starting value. DELETE keeps it where it was. If you’re scripting tests, this can matter.

TRUNCATE is transactional, which often surprises people. You can BEGIN TRAN; TRUNCATE TABLE X; ROLLBACK; and the data comes back. In this specific case, SQL Server does log the page deallocation metadata — just very compactly.

Finding who references what

The question “if I drop this column, what breaks?” comes up every sprint. Here’s the query:

SELECT
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name                   AS object_name,
    o.type_desc              AS object_type
FROM sys.sql_modules AS m
JOIN sys.objects     AS o ON o.object_id = m.object_id
WHERE m.definition LIKE N'%YourColumnName%'
   OR m.definition LIKE N'%YourTableName%';

It searches every procedure, function, view, and trigger for text references. Not perfect — dynamic SQL is opaque to this — but 90% of “what breaks if I change this?” gets answered in thirty seconds.

Run this on your own machine

A safe playground for today’s patterns:

USE tempdb;
GO

-- Create in a schema so we can drop the whole schema at the end
CREATE SCHEMA Playground AUTHORIZATION dbo;
GO

-- 1. Idempotent create
IF OBJECT_ID(N'Playground.Orders', N'U') IS NULL
BEGIN
    CREATE TABLE Playground.Orders (
        OrderId   BIGINT IDENTITY(1,1) NOT NULL,
        Total     DECIMAL(19,4)         NOT NULL DEFAULT 0,
        CreatedAt DATETIME2(0)          NOT NULL DEFAULT SYSUTCDATETIME(),
        CONSTRAINT pk_Orders PRIMARY KEY CLUSTERED (OrderId)
    );
END;

-- 2. Insert some data
INSERT INTO Playground.Orders (Total) VALUES (100.00), (250.75), (42.42);

-- 3. Add a NOT NULL column with a default (metadata-only)
ALTER TABLE Playground.Orders
ADD Status TINYINT NOT NULL CONSTRAINT df_Orders_Status DEFAULT 0;

-- 4. Rename a column
EXEC sp_rename 'Playground.Orders.Total', 'OrderTotal', 'COLUMN';

-- 5. Wrap a dangerous operation in a transaction
BEGIN TRAN;
    DELETE FROM Playground.Orders;
    SELECT COUNT(*) AS rows_left FROM Playground.Orders; -- 0
ROLLBACK;
SELECT COUNT(*) AS rows_back FROM Playground.Orders;     -- 3 again

-- 6. Find anything that references "Orders"
SELECT SCHEMA_NAME(o.schema_id) + '.' + o.name AS obj, o.type_desc
FROM sys.sql_modules m JOIN sys.objects o ON o.object_id = m.object_id
WHERE m.definition LIKE N'%Orders%';

-- 7. Cleanup
DROP TABLE Playground.Orders;
DROP SCHEMA Playground;

Run it, read each result, understand each step. Then do it one more time without looking. These patterns are your daily bread for as long as you work with SQL Server.

Next lesson: SELECT proper. Projection, aliasing, the sins of SELECT *, and the subtle thing nobody warned you about when you first learned SQL: the order clauses are written in is not the order they execute in. That small fact explains half the confusion beginners have with aggregates, and it’s lesson 6.

Search