Schema design in SQL Server affects storage efficiency, index size, query plans, and application correctness. Choose types deliberately — widening columns later can require expensive table rebuilds.

Numeric Types 

  CREATE TABLE dbo.Products (
    ProductID   BIGINT IDENTITY(1,1) PRIMARY KEY,
    SKU         VARCHAR(20) NOT NULL,
    Price       DECIMAL(10, 2) NOT NULL,
    Quantity    INT NOT NULL DEFAULT 0,
    WeightKg    DECIMAL(8, 3) NULL,
    IsActive    BIT NOT NULL DEFAULT 1,
    CONSTRAINT UQ_Products_SKU UNIQUE (SKU),
    CONSTRAINT CK_Products_Price CHECK (Price >= 0),
    CONSTRAINT CK_Products_Qty CHECK (Quantity >= 0)
);
Type Use Case Avoid
INT / BIGINT IDs, counts FLOAT for IDs
DECIMAL(p,s) Money, precise quantities MONEY (legacy quirks)
BIT Boolean flags CHAR(1) Y/N
FLOAT / REAL Scientific measurements Financial calculations

Rule: Never use floating point for currency — use DECIMAL(19,4) or similar.

String Types 

  CREATE TABLE dbo.Articles (
    ArticleID   INT IDENTITY(1,1) PRIMARY KEY,
    Slug        NVARCHAR(64) NOT NULL,
    Title       NVARCHAR(255) NOT NULL,
    Body        NVARCHAR(MAX) NULL,
    TagsJson    NVARCHAR(MAX) NULL,
    CONSTRAINT UQ_Articles_Slug UNIQUE (Slug)
);
Type Storage When to Use
VARCHAR(n) ASCII, 1 byte/char Codes, ASCII-only identifiers
NVARCHAR(n) Unicode, 2 bytes/char User-facing text, international
CHAR(n) / NCHAR(n) Fixed length Fixed codes (country ISO)
VARCHAR(MAX) / NVARCHAR(MAX) LOB storage Large text, JSON payloads

Use NVARCHAR for anything users type. Collation affects sort/compare — choose at database creation.

Date and Time 

  CREATE TABLE dbo.Events (
    EventID     INT IDENTITY(1,1) PRIMARY KEY,
    StartsAt    DATETIME2(3) NOT NULL,
    EndsAt      DATETIME2(3) NULL,
    CreatedAt   DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    TimeZoneId  NVARCHAR(50) NULL
);
Type Recommendation
DATETIME2(n) Default choice — precision 0–7
DATE Date-only (birthdays, reporting dates)
TIME(n) Time-only
DATETIMEOFFSET When offset matters (global scheduling)
DATETIME / SMALLDATETIME Legacy — avoid in new schema

Store UTC in DATETIME2 where possible; convert at application layer.

Binary and GUID 

  CREATE TABLE dbo.Attachments (
    AttachmentID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
    FileName     NVARCHAR(260) NOT NULL,
    Content      VARBINARY(MAX) NULL,
    ContentHash  BINARY(32) NULL  -- SHA-256
);
  • NEWID() — random GUID, random insert pattern (index fragmentation)
  • NEWSEQUENTIALID() — sequential GUIDs, better clustered index locality
  • Large blobs — consider FILESTREAM or Azure Blob with URL reference

Constraints and Referential Integrity 

  CREATE TABLE dbo.Orders (
    OrderID     INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID  INT NOT NULL,
    Total       DECIMAL(18,2) NOT NULL,
    Status      NVARCHAR(20) NOT NULL DEFAULT N'Pending',
    CONSTRAINT FK_Orders_Customer FOREIGN KEY (CustomerID)
        REFERENCES dbo.Customers (CustomerID),
    CONSTRAINT CK_Orders_Status CHECK (Status IN (N'Pending', N'Paid', N'Shipped', N'Cancelled'))
);

CREATE TABLE dbo.OrderItems (
    OrderItemID INT IDENTITY(1,1) PRIMARY KEY,
    OrderID     INT NOT NULL,
    ProductID   INT NOT NULL,
    Quantity    INT NOT NULL,
    UnitPrice   DECIMAL(10,2) NOT NULL,
    CONSTRAINT FK_OrderItems_Order FOREIGN KEY (OrderID) REFERENCES dbo.Orders (OrderID),
    CONSTRAINT FK_OrderItems_Product FOREIGN KEY (ProductID) REFERENCES dbo.Products (ProductID),
    CONSTRAINT CK_OrderItems_Qty CHECK (Quantity > 0),
    CONSTRAINT UQ_OrderItems_OrderProduct UNIQUE (OrderID, ProductID)
);

Identity vs Sequences 

  -- IDENTITY — simple auto-increment on column
CREATE TABLE dbo.Logs (
    LogID BIGINT IDENTITY(1,1) PRIMARY KEY,
    Message NVARCHAR(500) NOT NULL
);

-- SEQUENCE — share across tables, explicit NEXT VALUE
CREATE SEQUENCE dbo.OrderNumberSeq AS INT START WITH 100000 INCREMENT BY 1;

INSERT INTO dbo.Orders (OrderNumber, CustomerID, Total)
VALUES (NEXT VALUE FOR dbo.OrderNumberSeq, 1, 99.99);

Use sequences when multiple tables need non-overlapping numeric IDs or gap control matters.

Computed and Persisted Columns 

  CREATE TABLE dbo.Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName  NVARCHAR(50) NOT NULL,
    LastName   NVARCHAR(50) NOT NULL,
    FullName   AS (FirstName + N' ' + LastName) PERSISTED,
    AnnualSalary DECIMAL(12,2) NOT NULL,
    MonthlySalary AS (AnnualSalary / 12) PERSISTED
);

CREATE INDEX IX_Employees_FullName ON dbo.Employees (FullName);

PERSISTED stores the value — indexable. Non-persisted computed columns are calculated at query time.

JSON Columns (SQL Server 2016+) 

  ALTER TABLE dbo.Articles ADD Metadata AS JSON;

UPDATE dbo.Articles
SET Metadata = N'{"author":"Alice","tags":["sql","database"]}'
WHERE ArticleID = 1;

SELECT ArticleID, Title, JSON_VALUE(Metadata, '$.author') AS author
FROM dbo.Articles
WHERE JSON_VALUE(Metadata, '$.author') = N'Alice';

Add computed columns on JSON paths with indexes for frequent filters — avoid scanning NVARCHAR(MAX).

Temporal Tables (System-Versioned) 

  CREATE TABLE dbo.ProductsHistory (
    ProductID INT NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(10,2) NOT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
);

CREATE TABLE dbo.Products (
    ProductID INT PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(10,2) NOT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductsHistory));

Automatic audit of row changes — valuable for compliance without application triggers.

Normalization Guidelines

  1. 3NF for OLTP — eliminate repeating groups and transitive dependencies
  2. Denormalize only after measurement — caching aggregates in indexed views or columnstore
  3. Separate hot and cold columns — wide rows hurt buffer pool efficiency
  4. Use lookup tables instead of magic strings — enforce with FK + CHECK

Common Mistakes

Mistake Impact
NVARCHAR(255) everywhere Wasted index space; wrong semantics
Nullable everything Ambiguous business logic
No PK on tables Heap fragmentation, no clustered index
TEXT / IMAGE types Deprecated — use (N)VARCHAR(MAX) / VARBINARY(MAX)
Wrong collation join Sort errors, index cannot be used

Performance Tips

  • Match column width to data — VARCHAR(20) for SKU beats VARCHAR(8000)
  • Clustered index on narrow, increasing key (IDENTITY or sequential GUID)
  • Avoid over-normalizing high-read dashboards — use views or replicas
  • Partition large tables by date range when maintenance windows require it

Production Scenario: Soft Deletes 

  ALTER TABLE dbo.Customers ADD IsDeleted BIT NOT NULL DEFAULT 0;
CREATE INDEX IX_Customers_Active ON dbo.Customers (CustomerID) WHERE IsDeleted = 0;

Filtered indexes keep active-user queries fast without scanning deleted rows.

Schema choices compound over years — invest time upfront and document naming conventions (dbo schema, PascalCase columns, _History suffix for temporal tables).