SQL Server Database Design Best Practices: A Practical Guide

Almost every performance problem, data-quality complaint and painful migration can be traced back to a design decision made early - often before a single row existed. Application code is easy to change; a production schema full of live data is not. This guide walks through the database design decisions that matter most in Microsoft SQL Server, and how to make them so your database stays fast, correct and easy to extend for years.

Normalization — and sensible denormalization

Normalization is the process of organising columns and tables so each fact is stored in exactly one place. For a transactional database it is the right default, and three forms cover almost everything you need:

• First normal form (1NF): every column holds a single, atomic value - no comma-separated lists, no repeating groups like Phone1, Phone2, Phone3.
• Second normal form (2NF): every non-key column depends on the whole primary key, not just part of a composite key.
• Third normal form (3NF): non-key columns depend only on the key, not on other non-key columns. Storing a customer’s city and the derived region in the same row breaks this.

Normalizing to 3NF removes redundancy, which in turn removes whole classes of update anomalies - you never have to change the same fact in five places and risk missing one. Denormalization is a deliberate, measured exception: you duplicate data to speed up a specific, proven read pattern, accepting the cost of keeping copies in sync. Do it because a real query needs it, never because modelling the data properly felt like too much work.

Keys and relationships

Every table needs a primary key - a column (or set of columns) that uniquely identifies each row and can never be null. Relationships between tables are expressed with foreign keys, which point at the primary key of another table and let SQL Server enforce that a child row can never reference a parent that doesn’t exist.

Surrogate versus natural keys

A natural key comes from real-world data - an email address, an ISBN, a tax number. A surrogate key is a meaningless, system-generated value such as an IDENTITY integer or a sequence. Surrogates are usually the safer primary key: they’re compact, stable, and never change when the business changes (people do change email addresses). The best practice is to use a surrogate as the primary key and keep a UNIQUE constraint on the natural key, so real-world uniqueness is still guaranteed. Narrow integer keys also keep your indexes small and fast, which matters because the clustered key is copied into every non-clustered index.

Choosing the right data types

Data types are not a formality - they decide storage size, index efficiency and correctness. The guiding rule is: pick the smallest type that correctly holds every value you’ll ever need.

• Use DATE for a date with no time, and DATETIME2 rather than the legacy DATETIME when you need time - it’s more precise and standards-compliant.
• Use DECIMAL (or NUMERIC) for money and any value where exactness matters. Never use FLOAT or REAL for currency - they’re approximate and introduce rounding errors.
• Size string columns to the data. A VARCHAR(100) email column beats a lazy NVARCHAR(MAX); oversized and MAX types can’t be indexed normally and bloat memory grants.
• Use NVARCHAR only when you genuinely need Unicode; VARCHAR halves the storage for plain ASCII data.
• Match integer width to range - TINYINT, SMALLINT, INT or BIGINT - rather than defaulting everything to BIGINT.

Constraints and referential integrity

The database should enforce its own rules. Relying on application code alone means every future app, script and import has to remember the rules perfectly - and one that forgets corrupts your data. SQL Server gives you declarative constraints that make invalid data impossible:

• NOT NULL - the column must always have a value. Only allow NULL where “unknown” or “not applicable” is a genuine, meaningful state.
• UNIQUE - no duplicate values, ideal for natural keys alongside a surrogate primary key.
• CHECK - a value must satisfy a rule, such as Quantity > 0 or a status restricted to a known set.
• DEFAULT - a sensible value supplied automatically, like SYSUTCDATETIME() for a created-at timestamp.
• FOREIGN KEY - guarantees referential integrity so orphaned rows can never exist.

Avoid nullable columns where they aren’t needed. Every unnecessary NULL forces defensive ISNULL/COALESCE logic, complicates unique constraints, and blurs the meaning of your data. A column that is always populated is simpler to query and easier to index.

A worked example

Here’s a small but well-formed pair of tables showing keys, correct types and constraints working together - the kind of foundation we build under every application:

CREATE TABLE dbo.Customer (
    CustomerId   INT            IDENTITY(1,1) NOT NULL,
    Email        VARCHAR(256)   NOT NULL,
    FullName     NVARCHAR(120)  NOT NULL,
    CreatedUtc   DATETIME2(3)    NOT NULL
        CONSTRAINT DF_Customer_CreatedUtc DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_Customer PRIMARY KEY (CustomerId),
    CONSTRAINT UQ_Customer_Email UNIQUE (Email)
);

CREATE TABLE dbo.SalesOrder (
    OrderId      INT            IDENTITY(1,1) NOT NULL,
    CustomerId   INT            NOT NULL,
    OrderDate    DATE           NOT NULL,
    TotalAmount  DECIMAL(12,2)  NOT NULL,
    Status       VARCHAR(20)    NOT NULL,
    CONSTRAINT PK_SalesOrder PRIMARY KEY (OrderId),
    CONSTRAINT FK_SalesOrder_Customer FOREIGN KEY (CustomerId)
        REFERENCES dbo.Customer (CustomerId),
    CONSTRAINT CK_SalesOrder_Total CHECK (TotalAmount >= 0),
    CONSTRAINT CK_SalesOrder_Status CHECK (Status IN ('Pending','Paid','Shipped','Cancelled'))
);

Notice how much correctness is guaranteed by the schema alone: emails are unique, totals can’t go negative, statuses are restricted to a known set, timestamps default automatically, and no order can reference a customer that doesn’t exist. None of that depends on application code getting it right every time.

A deliberate indexing strategy

Indexing belongs in the design conversation, but it must be deliberate - not a reflex. By default the primary key creates a clustered index that physically orders the table, and that’s often a sensible clustering key when it’s a narrow, ever-increasing integer. Add non-clustered indexes to support the columns your queries actually filter, join and sort on.

The opposite failure is just as real: over-indexing. Every index has to be maintained on every insert, update and delete, so a table smothered in indexes pays a heavy write penalty and wastes storage. Index for the queries you truly run, then verify with usage statistics. Indexing is a big enough topic to deserve its own treatment - our SQL Server indexing guide covers clustered versus non-clustered indexes, covering indexes and column order in depth.

Common mistakes to avoid

• No foreign keys. Skipping them “for speed” quietly lets orphaned and inconsistent data accumulate until reports stop reconciling.
• Everything is NVARCHAR(MAX). Oversized and MAX types waste space, defeat normal indexing and inflate memory grants.
• FLOAT for money. Approximate types cause rounding errors that surface as accounting discrepancies no one can explain.
• Inconsistent naming. Mixing tblCustomers, Customer and customers_data makes the schema hard to learn and error-prone to query.
• Nullable everything. Allowing NULL where a value always exists forces defensive code everywhere and hides real data problems.

Best practices to follow

1. Model the data before you write code. Sketch entities, relationships and keys first; the schema is the foundation everything rests on.
2. Adopt one naming convention and never break it. Consistent, singular, PascalCase table and column names make the schema self-documenting.
3. Enforce rules with constraints. Let the database guarantee integrity so no application can bypass it.
4. Choose the smallest correct data type. It compounds into smaller indexes, less I/O and faster queries.
5. Document the schema. Keep a data dictionary and extended properties so the next engineer understands intent, not just structure.

Getting these foundations right is exactly what our SQL Server development service exists to do - from first data model to a tuned, documented, production-ready database.

Frequently asked questions