DDL: Tables and Constraints

Referential actions: NO ACTION (default), CASCADE, SET NULL, SET DEFAULT.

5.1 Creating Tables

CREATE TABLE dbo.Customers
(
    CustomerID   INT IDENTITY(1,1)   NOT NULL,
    FirstName    NVARCHAR(50)        NOT NULL,
    LastName     NVARCHAR(50)        NOT NULL,
    Email        VARCHAR(255)        NULL,
    City         NVARCHAR(60)        NULL,
    Country      NVARCHAR(60)        NOT NULL CONSTRAINT DF_Customers_Country DEFAULT (N'India'),
    CreatedAt    DATETIME2(0)        NOT NULL CONSTRAINT DF_Customers_CreatedAt DEFAULT (SYSDATETIME()),
    CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerID),
    CONSTRAINT UQ_Customers_Email UNIQUE (Email)
);

5.2 Constraints

Value is required Unique and not null. One per table. Creates a clustered index by default.

Unique values. Allows one NULL.

Validates against a Boolean expression Value used when none is supplied

Referential actions: NO ACTION (default), CASCADE, SET NULL, SET DEFAULT.

CREATE TABLE dbo.Categories
(
    CategoryID       INT IDENTITY PRIMARY KEY,
    CategoryName     NVARCHAR(100) NOT NULL UNIQUE,
    ParentCategoryID INT NULL REFERENCES dbo.Categories(CategoryID)   -- self reference
);

CREATE TABLE dbo.Products
(
    ProductID    INT IDENTITY PRIMARY KEY,
    ProductName  NVARCHAR(150) NOT NULL,
    CategoryID   INT NOT NULL,
    UnitPrice    DECIMAL(10,2) NOT NULL CONSTRAINT CK_Products_Price CHECK (UnitPrice >= 0),
    Stock        INT NOT NULL DEFAULT 0 CHECK (Stock >= 0),
    IsActive     BIT NOT NULL DEFAULT 1,
    CONSTRAINT FK_Products_Categories FOREIGN KEY (CategoryID)
        REFERENCES dbo.Categories(CategoryID)
        ON DELETE NO ACTION ON UPDATE CASCADE
);

CREATE TABLE dbo.Employees
(
    EmployeeID INT IDENTITY PRIMARY KEY,
    FullName   NVARCHAR(100) NOT NULL,
    ManagerID  INT NULL REFERENCES dbo.Employees(EmployeeID),
    HireDate   DATE NOT NULL,
    Salary     DECIMAL(12,2) NOT NULL,
    DeptID     INT NULL
);

CREATE TABLE dbo.Orders
(
    OrderID    INT IDENTITY PRIMARY KEY,
    CustomerID INT NOT NULL REFERENCES dbo.Customers(CustomerID),
    EmployeeID INT NULL REFERENCES dbo.Employees(EmployeeID),
    OrderDate  DATET
…

5.3 IDENTITY

Identity values can have gaps caused by rollbacks, restarts, and identity caching.

SELECT SCOPE_IDENTITY();             -- last identity value in the current scope (preferred)
SELECT @@IDENTITY;                   -- last value in the session (affected by triggers)
SELECT IDENT_CURRENT('dbo.Orders');  -- last value for a table, from any session
SET IDENTITY_INSERT dbo.Customers ON;   -- allow explicit values
DBCC CHECKIDENT ('dbo.Customers', RESEED, 1000);

5.4 Altering Tables

ALTER TABLE dbo.Customers ADD Phone VARCHAR(20) NULL;
ALTER TABLE dbo.Customers ALTER COLUMN Phone VARCHAR(30) NOT NULL;
ALTER TABLE dbo.Customers DROP COLUMN Phone;
ALTER TABLE dbo.Customers ADD CONSTRAINT CK_Email CHECK (Email LIKE '%_@_%._%');
ALTER TABLE dbo.Customers DROP CONSTRAINT CK_Email;
ALTER TABLE dbo.Orders NOCHECK CONSTRAINT ALL;        -- disable FKs/CHECKs
ALTER TABLE dbo.Orders WITH CHECK CHECK CONSTRAINT ALL; -- re-enable and validate (keeps them "trusted")
EXEC sp_rename 'dbo.Customers.City', 'CityName', 'COLUMN';

5.5 DROP vs TRUNCATE vs DELETE

Allowed with FK references Yes (inside a transaction)

TRUNCATE TABLE dbo.StagingSales;
TRUNCATE TABLE dbo.SalesPartitioned WITH (PARTITIONS (1 TO 3)); -- 2016+
DROP TABLE IF EXISTS dbo.StagingSales;

5.6 Temporary Tables and Table Variables

@Table variable No (deferred compilation since 2019) Inline PK/UNIQUE/INDEX only Not rolled back

CREATE TABLE #Temp (ID INT, Name NVARCHAR(50));    -- local, visible to this session only
CREATE TABLE ##GlobalTemp (ID INT);                -- global, visible to all sessions
DECLARE @T TABLE (ID INT PRIMARY KEY, Name NVARCHAR(50));  -- table variable

5.7 Other Table Options

SPARSE columns: optimized storage for mostly-NULL columns.

Column sets: an XML representation of the sparse columns.

Data compression: WITH (DATA_COMPRESSION = ROW | PAGE).

Table on a filegroup: CREATE TABLE ... ON FG_Archive.

Graph tables, temporal tables, memory-optimized tables: see later modules.