Creating Databases & Tables

Everything in SQL Server starts with a CREATE statement. Before you can store a single row of data, you need a database to hold it and a table to organise it. The CREATE DATABASE and CREATE TABLE statements look simple on the surface — a few lines of T-SQL — but every option you specify or leave at its default has a direct consequence on storage layout, performance, data integrity, and how easily the database can be maintained as it grows. This lesson walks through creating the DataplexaStore database and all five of its tables from scratch, explaining the reasoning behind every decision so you understand not just the syntax but the thinking that produces a well-designed schema.

Creating a Database

The minimum valid CREATE DATABASE statement is just three words: CREATE DATABASE name. SQL Server fills in every detail from the model system database — the template that new databases are copied from. In a learning environment that is perfectly fine. In production, accepting defaults means your data file and log file land in the same folder as the system databases, grow by a percentage that causes increasingly long pause events, and start at a size that triggers a growth event the moment any real data arrives. The full CREATE DATABASE syntax lets you specify exactly where each file lives, how large it starts, and how it grows. The DataplexaStore database was created with sensible defaults in Lesson 5 — this section shows what a production-grade equivalent looks like and why each setting was chosen.

-- Drop and recreate DataplexaStore with explicit, production-aware settings
-- Run this on your Developer Edition instance to follow along

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'DataplexaStore')
BEGIN
    ALTER DATABASE DataplexaStore SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DataplexaStore;   -- disconnect all users, then drop cleanly
END

CREATE DATABASE DataplexaStore
ON PRIMARY (
    NAME        = 'DataplexaStore',             -- logical name used by SQL Server internally
    FILENAME    = 'C:\SQLData\DataplexaStore.mdf', -- dedicated data drive
    SIZE        = 64MB,                         -- pre-allocate to avoid early auto-growth
    FILEGROWTH  = 64MB                          -- fixed growth — never use % on data files
)
LOG ON (
    NAME        = 'DataplexaStore_log',         -- logical name for the log file
    FILENAME    = 'C:\SQLData\DataplexaStore_log.ldf', -- ideally a separate drive in production
    SIZE        = 32MB,                         -- pre-size the log for normal daily activity
    FILEGROWTH  = 32MB                          -- fixed growth on logs is critical
);

-- Switch context into the new database for all statements that follow
USE DataplexaStore;

-- Confirm the database was created with the settings we specified
SELECT
    name,
    type_desc,
    physical_name,
    size * 8 / 1024     AS size_mb,
    growth              AS growth_pages          -- divide by 128 to convert pages to MB
FROM sys.master_files
WHERE database_id = DB_ID('DataplexaStore');

• growth_pages of 8192 confirms the 64 MB fixed growth setting — 8192 pages × 8 KB per page = 64 MB per auto-growth event
• SET SINGLE_USER WITH ROLLBACK IMMEDIATE forces all other connections off the database before dropping it — without this, DROP DATABASE fails if any other session is connected
• The IF EXISTS guard makes the script safely re-runnable — if the database does not exist yet it skips the drop, if it does exist it cleanly removes it before recreating

Creating Tables — The Full Syntax

A CREATE TABLE statement defines the name of the table, every column with its data type and nullability, and any constraints that enforce data integrity. The order of columns in a CREATE TABLE statement determines the ordinal position SQL Server stores them in — while SQL Server can handle any order, the convention is to put the primary key first, followed by foreign key columns, then required columns, then optional columns. Constraints can be defined inline (directly after the column they apply to) or as separate table-level definitions at the bottom of the CREATE TABLE block. Inline constraints are concise and readable for simple cases. Table-level constraints are required when a constraint spans multiple columns — a composite primary key or a CHECK constraint that compares two columns — and are also preferred when you want to give the constraint an explicit name, which makes error messages far easier to read.

-- Create the Users table — the root of the DataplexaStore dependency tree
-- Users has no foreign keys, so it is created first

CREATE TABLE Users (
    UserID          INT             NOT NULL IDENTITY(1,1),  -- auto-generated PK, starts at 1
    FullName        NVARCHAR(100)   NOT NULL,                -- Unicode — supports all languages
    Email           NVARCHAR(150)   NOT NULL,                -- longer than name — email addresses vary
    City            NVARCHAR(100)   NOT NULL,
    Country         NVARCHAR(100)   NOT NULL,
    MembershipTier  NVARCHAR(20)    NOT NULL DEFAULT 'Standard', -- sensible default for new signups
    JoinDate        DATE            NOT NULL DEFAULT GETDATE(),  -- auto-stamp the signup date

    -- Table-level constraints — named explicitly for clear error messages
    CONSTRAINT PK_Users       PRIMARY KEY (UserID),
    CONSTRAINT UQ_Users_Email UNIQUE      (Email)           -- no two accounts share an email
);

• Naming constraints explicitly (PK_Users, UQ_Users_Email) means that when a violation occurs the error message says "violated constraint PK_Users" rather than a system-generated name like "PK__Users__1B0907CF" — a small discipline that saves significant debugging time
• DEFAULT 'Standard' on MembershipTier means the application never needs to send this value for new users — SQL Server fills it in automatically, reducing the chance of a missing-value bug in application code
• DATE rather than DATETIME2 on JoinDate reflects that time of day of signup is irrelevant — choosing the narrowest correct type keeps the schema self-documenting

Creating Tables with Foreign Keys

Foreign key constraints must be added after the parent table exists. In DataplexaStore the dependency chain is: Users and Products are parents; Orders depends on Users; OrderItems depends on both Orders and Products; Reviews depends on both Products and Users. This order must be followed strictly — attempting to create OrderItems before Orders exists will fail with a foreign key reference error. The REFERENCES keyword declares the relationship, and the ON DELETE and ON UPDATE clauses define what happens to child rows when a parent row is deleted or its key is updated. The safest default for business data is NO ACTION on both, which forces the application to delete child rows explicitly rather than having the database silently cascade deletions.

-- Create Products — also a parent table with no foreign keys

CREATE TABLE Products (
    ProductID    INT           NOT NULL IDENTITY(1,1),
    ProductName  NVARCHAR(100) NOT NULL,
    Category     NVARCHAR(50)  NOT NULL,
    Price        DECIMAL(10,2) NOT NULL,
    StockQty     INT           NOT NULL DEFAULT 0,

    CONSTRAINT PK_Products         PRIMARY KEY (ProductID),
    CONSTRAINT CK_Products_Price   CHECK (Price > 0),         -- price must be positive
    CONSTRAINT CK_Products_Stock   CHECK (StockQty >= 0)      -- stock cannot go negative
);

-- Create Orders — depends on Users
CREATE TABLE Orders (
    OrderID      INT           NOT NULL IDENTITY(1,1),
    UserID       INT           NOT NULL,                       -- FK to Users
    OrderDate    DATETIME2     NOT NULL DEFAULT GETDATE(),
    Status       NVARCHAR(20)  NOT NULL DEFAULT 'processing',
    TotalAmount  DECIMAL(10,2) NOT NULL,

    CONSTRAINT PK_Orders              PRIMARY KEY (OrderID),
    CONSTRAINT FK_Orders_Users        FOREIGN KEY (UserID) REFERENCES Users(UserID),
    CONSTRAINT CK_Orders_TotalAmount  CHECK (TotalAmount >= 0)
);

-- Create OrderItems — depends on both Orders and Products
CREATE TABLE OrderItems (
    OrderItemID  INT           NOT NULL IDENTITY(1,1),
    OrderID      INT           NOT NULL,                       -- FK to Orders
    ProductID    INT           NOT NULL,                       -- FK to Products
    Quantity     INT           NOT NULL,
    UnitPrice    DECIMAL(10,2) NOT NULL,

    CONSTRAINT PK_OrderItems            PRIMARY KEY (OrderItemID),
    CONSTRAINT FK_OrderItems_Orders     FOREIGN KEY (OrderID)   REFERENCES Orders(OrderID),
    CONSTRAINT FK_OrderItems_Products   FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    CONSTRAINT CK_OrderItems_Quantity   CHECK (Quantity > 0),
    CONSTRAINT CK_OrderItems_UnitPrice  CHECK (UnitPrice > 0)
);

-- Create Reviews — depends on both Products and Users
CREATE TABLE Reviews (
    ReviewID    INT            NOT NULL IDENTITY(1,1),
    ProductID   INT            NOT NULL,
    UserID      INT            NOT NULL,
    Rating      TINYINT        NOT NULL,
    ReviewText  NVARCHAR(1000) NULL,                           -- optional — not every review has text
    ReviewDate  DATE           NOT NULL DEFAULT GETDATE(),

    CONSTRAINT PK_Reviews          PRIMARY KEY (ReviewID),
    CONSTRAINT FK_Reviews_Products FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    CONSTRAINT FK_Reviews_Users    FOREIGN KEY (UserID)    REFERENCES Users(UserID),
    CONSTRAINT CK_Reviews_Rating   CHECK (Rating BETWEEN 1 AND 5)
);

• ReviewText is the only NULL column in the entire schema — it is NULL because a review without text is genuinely valid, not because the field was forgotten; every other NULL decision in this schema would represent missing required information
• TINYINT on Rating is a deliberate choice — valid values are 1 to 5, and TINYINT (1 byte, range 0–255) is the smallest type that covers this range, saving 3 bytes per row compared to INT
• The CHECK constraint on Rating uses BETWEEN rather than two separate comparisons — both are equivalent but BETWEEN makes the intent immediately readable: this column accepts only values from 1 to 5

Verifying the Schema

After creating a schema it is good practice to verify that every table, column, constraint, and relationship was created exactly as intended. Querying the information schema and system catalog views gives you a programmatic audit trail that is more reliable than visually inspecting table definitions in SSMS — especially in automated deployment scripts where silent failures would otherwise go unnoticed.

-- Verify all five tables exist
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

-- Verify all constraints were created with their correct names and types
SELECT
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME,
    tc.CONSTRAINT_TYPE,
    kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
ORDER BY tc.TABLE_NAME, tc.CONSTRAINT_TYPE, tc.CONSTRAINT_NAME;

• 17 constraints across 5 tables — every primary key, every foreign key, every CHECK constraint, and the UNIQUE constraint on Email all confirmed present with correct names
• CHECK constraints show NULL in the COLUMN_NAME column because they apply to the whole row rather than being tied to a specific key column — the constraint expression itself is stored in INFORMATION_SCHEMA.CHECK_CONSTRAINTS
• Running this verification query at the end of every deployment script catches any silent CREATE failure before the application goes live against the new schema

Modifying Tables with ALTER TABLE

Real schemas change over time. A business requirement arrives that needs a new column, a data type turns out to be too narrow, or a constraint needs to be added to an existing table. The ALTER TABLE statement handles all of these changes without dropping and recreating the table, which means existing data is preserved. Adding a nullable column to a large table is a metadata-only operation in SQL Server — it completes instantly regardless of table size. Adding a NOT NULL column requires a default value so existing rows have something to store. Widening a column (increasing the length of a VARCHAR) is also instant. Narrowing a column or changing a type requires that all existing data fits the new definition — if any row would be truncated or invalid, the ALTER fails and the table is left unchanged.

-- Add a phone number column to Users — nullable because existing rows have no phone data
ALTER TABLE Users
    ADD PhoneNumber NVARCHAR(20) NULL;          -- NULL allows existing rows to have no value

-- Add a constraint to the new column after the fact
ALTER TABLE Users
    ADD CONSTRAINT CK_Users_Phone
    CHECK (PhoneNumber IS NULL OR LEN(PhoneNumber) >= 7); -- either null or at least 7 digits

-- Widen an existing column — instant metadata operation, no data movement
ALTER TABLE Products
    ALTER COLUMN ProductName NVARCHAR(200) NOT NULL;  -- was 100, now 200 — existing data is fine

-- Drop the phone column we just added — clean up after the demonstration
ALTER TABLE Users
    DROP CONSTRAINT CK_Users_Phone;    -- must drop the constraint before dropping the column
ALTER TABLE Users
    DROP COLUMN PhoneNumber;

-- Confirm the Users table is back to its original shape
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Users'
ORDER BY ORDINAL_POSITION;

• A constraint must be dropped before the column it references can be dropped — SQL Server enforces this because the constraint definition refers to the column name; dropping the column without the constraint would leave a dangling reference in the system catalog
• Widening a column from NVARCHAR(100) to NVARCHAR(200) is a metadata-only change — SQL Server updates the catalog entry without touching any data pages, so it completes in milliseconds even on a table with 100 million rows
• Narrowing a column or changing a numeric type to one with less precision requires that every existing value fits — if a single row would be truncated the entire ALTER is rolled back and the table is unchanged

Summary Table

Practice Questions

Quiz

Next up - Constraints & Keys - Everything you need to know about enforcing data integrity at the database level.