Schemas, Tables & Constraints

A SQL Server database is divided into schemas — namespaces that group related objects. Tables, constraints, and computed columns are the building blocks of relational design.

Schema vs Database

Concept	Scope
Server (instance)	A SQL Server process; hosts many databases
Database	Independent unit of storage, security, backup
Schema	Namespace within a database (dbo, sales, hr, audit)
Object	Table, view, procedure, function — owned by a schema

The default schema is dbo. Reference objects with three or four parts:

SELECT * FROM employees;                -- defaults to current_user.default_schema
SELECT * FROM hr.employees;             -- schema.table
SELECT * FROM HR.hr.employees;          -- database.schema.table
SELECT * FROM Server1.HR.hr.employees;  -- linked-server.database.schema.table

Creating Schemas

CREATE SCHEMA hr   AUTHORIZATION dbo;
CREATE SCHEMA audit AUTHORIZATION dbo;

-- Move a table to a different schema
ALTER SCHEMA audit TRANSFER dbo.employee_log;

CREATE TABLE

CREATE TABLE hr.employees (
    employee_id     INT             IDENTITY(1,1) NOT NULL,
    first_name      NVARCHAR(50)    NOT NULL,
    last_name       NVARCHAR(50)    NOT NULL,
    email           NVARCHAR(100)   NOT NULL,
    phone_number    VARCHAR(20)         NULL,
    hire_date       DATE            NOT NULL DEFAULT (CAST(GETDATE() AS DATE)),
    job_id          VARCHAR(10)     NOT NULL,
    salary          DECIMAL(10, 2)  NOT NULL,
    commission_pct  DECIMAL(4, 2)       NULL,
    manager_id      INT                 NULL,
    department_id   INT                 NULL,

    CONSTRAINT PK_employees PRIMARY KEY (employee_id),
    CONSTRAINT UQ_employees_email UNIQUE (email),
    CONSTRAINT CK_employees_salary CHECK (salary >= 0),
    CONSTRAINT FK_employees_manager
        FOREIGN KEY (manager_id) REFERENCES hr.employees(employee_id),
    CONSTRAINT FK_employees_department
        FOREIGN KEY (department_id) REFERENCES hr.departments(department_id)
);

Constraints

Constraint	Enforces
PRIMARY KEY	Unique, NOT NULL — typically backed by a clustered index
UNIQUE	Unique values; allows one NULL (in SQL Server)
FOREIGN KEY	References a PK/unique key in another (or same) table
CHECK	Boolean expression must be true
DEFAULT	Value supplied when column omitted in INSERT
NOT NULL	Column-level no-NULL rule

Adding Constraints After the Fact

ALTER TABLE hr.employees
    ADD CONSTRAINT CK_employees_salary_min CHECK (salary >= 1500);

ALTER TABLE hr.employees
    ADD CONSTRAINT FK_employees_dept
        FOREIGN KEY (department_id)
        REFERENCES hr.departments(department_id)
        ON UPDATE CASCADE
        ON DELETE NO ACTION;

-- Disable a check temporarily (rarely a good idea — table becomes "untrusted")
ALTER TABLE hr.employees NOCHECK CONSTRAINT CK_employees_salary_min;

-- Re-enable
ALTER TABLE hr.employees WITH CHECK CHECK CONSTRAINT CK_employees_salary_min;

Foreign Key Actions

Action	Behaviour on parent UPDATE/DELETE
NO ACTION (default)	Reject if children exist
CASCADE	Apply same change to children
SET NULL	Set child FK column to NULL
SET DEFAULT	Set child FK column to its default

ALTER TABLE

-- Add a column
ALTER TABLE hr.employees ADD nationality VARCHAR(2) NULL;

-- Drop a column
ALTER TABLE hr.employees DROP COLUMN nationality;

-- Change a column's type (must be compatible / no data loss)
ALTER TABLE hr.employees ALTER COLUMN phone_number VARCHAR(30) NULL;

-- Rename a column / table (sp_rename — limited; consider drop/recreate)
EXEC sp_rename 'hr.employees.phone_number', 'phone', 'COLUMN';

Computed Columns

A computed column is calculated from other columns. By default it is virtual (computed at read time). Add PERSISTED to materialise the value (and to allow it to be indexed when the expression is deterministic).

CREATE TABLE hr.employees_v2 (
    employee_id  INT IDENTITY PRIMARY KEY,
    first_name   NVARCHAR(50) NOT NULL,
    last_name    NVARCHAR(50) NOT NULL,
    full_name    AS (first_name + N' ' + last_name) PERSISTED,
    salary       DECIMAL(10,2) NOT NULL,
    annual_bonus AS (salary * 0.10),                  -- virtual
    salary_band  AS (CASE
                       WHEN salary < 5000  THEN 'Junior'
                       WHEN salary < 12000 THEN 'Mid'
                       ELSE 'Senior'
                     END) PERSISTED
);

-- Index the persisted computed column for fast lookups
CREATE INDEX IX_employees_v2_fullname ON hr.employees_v2 (full_name);

Filegroups (Brief Overview)

A database stores its data in one or more files, grouped into filegroups. The default PRIMARY filegroup holds system catalogs and any object created without an explicit filegroup. You can place a table or index on a specific filegroup to spread I/O across drives:

ALTER DATABASE HR ADD FILEGROUP FG_archive;

ALTER DATABASE HR
ADD FILE (
    NAME = 'HR_archive',
    FILENAME = 'D:\Data\HR_archive.ndf',
    SIZE = 1GB
) TO FILEGROUP FG_archive;

CREATE TABLE hr.audit_log (
    audit_id BIGINT IDENTITY PRIMARY KEY,
    payload  NVARCHAR(MAX)
) ON FG_archive;

Filegroups are also the unit of partial backup/restore and table partitioning.

Best Practices

• Always use a non-default schema (e.g. hr., sales.) for organisation and security boundaries.
• Name constraints explicitly (PK_table, FK_child_parent) so error messages are useful and DDL diffs are deterministic.
• Use PERSISTED computed columns when you need to index them or the expression is expensive.
• Foreign keys without NOCHECK are "trusted" — the optimiser uses this fact for plans; avoid NOCHECK unless absolutely necessary.

Summary

• A schema is a namespace inside a database; reference objects as schema.table.
• Constraints (PK, FK, UNIQUE, CHECK, DEFAULT, NOT NULL) enforce data integrity.
• ALTER TABLE adds/drops/changes columns and constraints.
• Computed columns can be virtual (default) or PERSISTED (storage + indexable).
• Filegroups organise data files for I/O distribution and partial backup/restore.