Choosing the Right SQL Server Data Types (and Why It Matters for Performance)

Data types feel like a minor detail when you create a table, and they are expensive to change once the table holds millions of rows. The right types make rows smaller, so more fit in memory, indexes shrink, and queries read less. The wrong ones cause subtle bugs, implicit conversions and wasted memory grants.

Text: VARCHAR vs NVARCHAR

• NVARCHAR stores Unicode, so it handles any language, at about 2 bytes per character for most text.
• VARCHAR uses 1 byte per character for characters in its code page. From SQL Server 2019, a UTF-8 collation lets VARCHAR store Unicode too, which can save space for mostly-English text.
• Use NVARCHAR for names, addresses and free text that may contain any language. Use VARCHAR for codes you control, such as EmployeeCode, country codes or status values.
• Match the type in your application parameters. Comparing a VARCHAR column with an NVARCHAR parameter can force a conversion and an index scan.

Realistic lengths, not MAX

Do not declare every string as NVARCHAR(MAX) or VARCHAR(8000). SQL Server sizes memory grants assuming variable-length columns are about half full, so oversized declarations waste memory in sorts and hashes. MAX columns also cannot be index key columns. Pick a length that fits the real data with a little headroom.

Dates and times

Type	Size	Precision	Use for
DATE	3 bytes	1 day	Birth dates, hire dates, pay periods
DATETIME2(n)	6 to 8 bytes	Up to 100 ns	New timestamp columns
DATETIME	8 bytes	About 3 ms (rounded)	Legacy only
DATETIMEOFFSET	8 to 10 bytes	Up to 100 ns + time zone	Times that must keep their offset

Use DATE when there is no time part, and DATETIME2 for timestamps (often DATETIME2(0) or (3) is enough). Store UTC (SYSUTCDATETIME()) and convert for display.

Money: DECIMAL, never FLOAT

FLOAT and REAL are approximate: 0.1 + 0.2 is not exactly 0.3, and payroll totals drift by small amounts. Use DECIMAL(p, s), for example DECIMAL(18,2) for amounts and more scale for rates. The MONEY type is exact but has rounding surprises in division, so DECIMAL is the safer default.

Integer keys: INT vs BIGINT

• INT (4 bytes) tops out at about 2.1 billion. That sounds like a lot until a busy log or transaction table runs out, which is a painful emergency migration.
• BIGINT (8 bytes) is the safe choice for high-volume tables. For small lookup tables, INT, SMALLINT or TINYINT saves space in every row and index that references them.

GUIDs as clustered keys

UNIQUEIDENTIFIER values are 16 bytes, and random GUIDs from NEWID() make inserts land all over the clustered index, causing page splits and fragmentation. They are also copied into every nonclustered index. If you need GUIDs, consider NEWSEQUENTIALID(), or keep a narrow INT/BIGINT clustered key and store the GUID as a separate unique column.

Flags and small values

• BIT for true/false. SQL Server packs up to 8 bit columns into one byte.
• TINYINT or a small code column with a lookup table for statuses, rather than long strings repeated in every row.

Keep types consistent across tables

A column used to join tables, such as EmployeeId, should have exactly the same type everywhere, including in foreign keys, temp tables and stored procedure parameters. Mismatches cause implicit conversions that can prevent index seeks.

Quick checklist

• NVARCHAR for human text, VARCHAR for codes; realistic lengths.
• DATE and DATETIME2, stored in UTC.
• DECIMAL for money; never FLOAT.
• BIGINT for high-volume keys; small types for lookups.
• Avoid random GUID clustered keys.
• Same type for the same column everywhere.

Related reading:

• SQL Server Index Tuning for Beginners
• Entity Framework Core Performance: 8 Common Mistakes
• SQL Server Parameter Sniffing: What It Is and How to Fix It