SQL Data Definition – Best Practices

Introduction

Writing clean, maintainable, and performant Data Definition Language (DDL) scripts is essential for any relational database project. This guide outlines best‑practice recommendations for SQL Server, Azure SQL Database, and compatible platforms.

Naming Conventions

Consistent naming improves readability and reduces errors. Follow these rules:

Use snake_case or PascalCase consistently.
Prefix object types (e.g., tbl_ for tables, idx_ for indexes) only when it adds clarity.
Never use reserved keywords without quoting.

-- Good
CREATE TABLE dbo.Employee
(
    EmployeeID   INT          NOT NULL PRIMARY KEY,
    FirstName    NVARCHAR(50) NOT NULL,
    LastName     NVARCHAR(50) NOT NULL,
    HireDate     DATE         NULL
);

-- Bad
CREATE TABLE dbo.tblEmp
(
    emp_id   INT NOT NULL,
    fName    NVARCHAR(50),
    lName    NVARCHAR(50)
);

Schema Design

Separate logical groups of objects using schemas. Common schemas include dbo, audit, and security.

CREATE SCHEMA audit AUTHORIZATION dbo;

CREATE TABLE audit.LoginHistory
(
    LoginID      BIGINT IDENTITY(1,1) PRIMARY KEY,
    UserName     NVARCHAR(128) NOT NULL,
    LoginTime    DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
    SourceIP     VARCHAR(45) NULL
);

Indexing Strategies

Indexes should be created deliberately. Consider covering indexes, filtered indexes, and columnstore indexes where appropriate.

Scenario	Recommended Index
High‑volume OLTP queries on `OrderDate`	Non‑clustered filtered index on `OrderDate` WHERE `IsDeleted = 0`
Aggregations over large fact tables	Columnstore index
Lookup by composite key	Composite non‑clustered index matching query predicates

Constraints & Defaults

Define explicit constraints to enforce data integrity.

ALTER TABLE dbo.Employee
ADD CONSTRAINT CK_Employee_HireDate
CHECK (HireDate IS NULL OR HireDate >= '1900-01-01');

ALTER TABLE dbo.Employee
ADD CONSTRAINT DF_Employee_IsActive
DEFAULT (1) FOR IsActive;

Documentation Practices

Every object should have an extended property for documentation.

EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'Table storing employee personal data.',
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE',  @level1name = N'Employee';

Common Pitfalls

Avoid SELECT * in view definitions.
Never drop and recreate tables in production; use ALTER statements.
Beware of implicit conversions that can stall indexes.

Microsoft Docs