DEV Community

LingamSuyambu
LingamSuyambu

Posted on Originally published at sqlcloudlab.blogspot.com

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

Originally published at SQL & Cloud Lab.

This post was drafted with AI assistance.

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.

Choosing the right SQL Server data types

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:


More SQL Server guides on my blog, SQL & Cloud Lab.

Top comments (0)