SQL Server Data Types: How to Choose the Right Type and Avoid Implicit Conversions

Choosing data types is one of the easiest decisions to skip in table design, and one of the hardest to change later. A type wider than needed takes a few extra bytes per row; across millions of rows that becomes gigabytes, more pages read, bigger indexes and longer backups. The wrong type causes subtler problems too: calculations that lose cents, queries that can’t use an index, characters that get mangled.

This guide covers SQL Server’s main data types, when to choose which, and the most common mistakes.

Integers

Pick the smallest type that fits the range, but account for growth. When an int IDENTITY column on a busy table reaches 2.1 billion, inserts stop, and moving to bigint on a large table takes hours. For fast-growing log and transaction tables, starting with bigint is safer.

Decimals and Money

• decimal(p, s) / numeric: stores exact values. The right choice for all financial data: amounts, quantities, rates.
• float / real: stores approximate values. Fine for scientific measurements; definitely not for money.
• money / smallmoney: stores a fixed 4 decimal places and can cause rounding issues in multiplication and division. decimal is usually more predictable.

Text: char, varchar, nchar, nvarchar

With a single-byte collation, varchar can store the characters of that code page; characters outside it (other alphabets, emojis) are saved as “?”. For multilingual data or free text from outside, use nvarchar. From SQL Server 2019, collations ending in _UTF8 let varchar columns store Unicode as UTF-8. See our collation guide for choosing a collation.

varchar(max) and nvarchar(max) should only be used for data that can exceed 8,000 bytes (4,000 characters for nvarchar). Declaring every column as max stops them from being used as index keys and inflates memory estimates.

Date and Time

Use date when you only need the date. When you need the time too, datetime2 offers both a wider range and adjustable precision. If data arrives from different time zones, datetimeoffset or storing in UTC keeps things consistent.

Other Important Types

• bit: for yes/no values; up to 8 bit columns in a table share a single byte.
• uniqueidentifier: a 16-byte GUID. Using random GUIDs as the clustered index key causes constant page splits and fragmentation; if needed, use NEWSEQUENTIALID() or a separate integer key.
• varbinary(max): binary data. Storing large files in the database grows backups and restore times; keeping the file outside and storing its path is often better.
• xml and json: semi-structured data. SQL Server 2025 introduces a native json data type; on older versions, JSON is stored in nvarchar.
• vector: introduced in SQL Server 2025, designed to store embedding vectors and run similarity searches.
• text, ntext, image: deprecated; use varchar(max), nvarchar(max) and varbinary(max) instead.

Implicit Conversion: The Invisible Performance Problem

When two compared values have different types, SQL Server converts one to the other. If the converted side is an indexed column, an index seek isn’t possible and the table is scanned. The classic example is comparing a varchar column with an nvarchar parameter:

-- CustomerCode is varchar(20) and indexed
DECLARE @code nvarchar(20) = N'C-1001';

-- The column is converted to nvarchar; depending on the collation it can fall back to a scan
SELECT * FROM dbo.Customer WHERE CustomerCode = @code;

-- Parameter matches the column type: index seek
DECLARE @code2 varchar(20) = 'C-1001';
SELECT * FROM dbo.Customer WHERE CustomerCode = @code2;

A CONVERT_IMPLICIT warning in the execution plan is the sign of this problem. Matching parameter types to column types on the application side (and knowing that ORMs often send string parameters as nvarchar by default) fixes it at the root. The same applies to join columns: columns joined to each other should have the same type.

SELECT c.name AS column_name,
       COUNT(DISTINCT TYPE_NAME(c.user_type_id) + '(' + CAST(c.max_length AS varchar(10)) + ')') AS type_variants,
       STRING_AGG(CONVERT(nvarchar(max), OBJECT_NAME(c.object_id) + ':' + TYPE_NAME(c.user_type_id)), ', ') AS usages
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
GROUP BY c.name
HAVING COUNT(DISTINCT TYPE_NAME(c.user_type_id) + '(' + CAST(c.max_length AS varchar(10)) + ')') > 1
ORDER BY type_variants DESC;

Checklist

• Integer types fit the value range and expected growth.
• Money and quantities are decimal; float only for approximate values.
• Multilingual text is nvarchar; max types only where needed.
• New designs use date or datetime2 instead of datetime.
• Random GUIDs aren’t clustered index keys.
• text, ntext and image aren’t used.
• Joined columns and application parameters match the column types.