What Is a Data Type, and Why the Wrong One Costs You

A data type decides three things about a column. What it is allowed to hold, how much room each value takes, and how SQL Server compares it to anything else. Most people think about the first for a moment and never think about the other two. Those two are where the bills come from.

Storage Is Not an Abstraction

Every value you store occupies real bytes on a real page, and the type decides how many. DATALENGTH shows you exactly. I ran this on SQL Server 2025:

SELECT DATALENGTH(CAST('Pinal' AS varchar(50)))  AS varchar_bytes,
       DATALENGTH(CAST('Pinal' AS nvarchar(50))) AS nvarchar_bytes,
       DATALENGTH(CAST('Pinal' AS char(50)))     AS char_bytes;

varchar_bytes  nvarchar_bytes  char_bytes
5              10              50

The same five letters, stored three ways. VARCHAR uses one byte per character. NVARCHAR uses two, because it can hold any alphabet. CHAR(50) pads to the full width whether you need it or not.

On one row this is nothing. On fifty million rows it decides whether the table fits in memory. Everything SQL Server does gets slower when pages stop fitting.

Dates Are Worse Than You Think

SELECT DATALENGTH(CAST(SYSDATETIME() AS datetime))     AS datetime_bytes,
       DATALENGTH(CAST(SYSDATETIME() AS date))         AS date_bytes,
       DATALENGTH(CAST(SYSDATETIME() AS datetime2(0))) AS datetime2_0_bytes;

datetime_bytes  date_bytes  datetime2_0_bytes
8               3           6

A birth date does not need a time. Storing it as DATE costs three bytes instead of eight. It also removes a whole category of bug, where a row saved at half past nine does not match a search for that day.

DATETIME2 is better than DATETIME when you do need a time. You choose the precision and you get the full date range. DATETIME is still everywhere because it has been around forever.

Float Will Not Do What You Want

This one causes arguments in meetings.

SELECT CAST(0.1 AS float) + CAST(0.2 AS float)               AS float_math,
       CAST(0.1 AS decimal(3,1)) + CAST(0.2 AS decimal(3,1)) AS decimal_math;

SELECT CASE WHEN CAST(0.1 AS float) + CAST(0.2 AS float) = CAST(0.3 AS float)
            THEN 'equal' ELSE 'not equal' END AS float_compare;

float_math           decimal_math
0.30000000000000004  .3

float_compare
not equal

FLOAT is an approximation. It is excellent for measurements, where a tiny error is meaningless. It is wrong for money, where it is not. Rounding in the report does not fix the comparison in the WHERE clause.

Use DECIMAL for anything counted or paid. Use FLOAT for anything measured.

The Conversion That Kills an Index

Here is the one that ends up on my invoice. Suppose a column is VARCHAR and the application sends NVARCHAR, which happens by default with a great many drivers.

SELECT * FROM dbo.Customer WHERE AccountNumber = N'AW00000001';

SQL Server will not compare two different types directly. One side has to be converted, and fixed rules decide which. NVARCHAR wins. So the conversion is applied to your column, on every row, before the comparison happens.

An index on that column can no longer be seeked, only scanned. A query that touched a handful of rows now reads the whole table. Nothing in the query text looks wrong. The plan shows it, with a warning on the operator, and that is the only clue.

This is the single most common cause of a query being slow for no apparent reason that I find on client servers.

A Short Set of Rules

Pick the smallest type that holds every value you will ever need, and no smaller. Use VARCHAR unless the column really holds more than one alphabet. Use DATE for dates and DATETIME2 when you need a time. Use DECIMAL for money and FLOAT for measurements. Use INT for identifiers, and BIGINT only when INT will run out, which is at about two billion.

Above all, make the same thing the same type everywhere. If CustomerID is INT in one table, it should be INT in all of them. Half the conversion problems I find come from two tables disagreeing about what a customer number is.

Changing a type later means rewriting the table and rebuilding its indexes. It also means checking every procedure that touches it. Ten seconds of thought at design time is worth a weekend later.

A data type is not a label on a column, it is a decision about how every future query will be allowed to run.

This post was rewritten from scratch in September 2026. The original, published on 2014-09-28, was a short announcement about something that no longer exists. The address is the same, the subject is now a basic idea worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.