.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
Updated and tested 9 October 2026
Column types are the one part of a schema you cannot walk back on a whim. An index goes in with a routine deploy. A stored procedure gets rewritten over lunch. Changing varchar(50) to nvarchar(100) on a table with 200 million rows and four foreign keys pointing at it means a maintenance window, a rollback plan and an argument with someone from the application team about encoding.
So the twenty minutes you spend on the choice up front are cheap. The second half of this is the conversion rules that fire when two types meet in an expression, since that is where wrong answers come from. Data types in MSSQL are documented well and read badly, which is most of why the precedence list below surprises people. Examples use the same schema as the rest of this series: dbo.customers, dbo.orders, dbo.order_items and dbo.employees.
Choosing between the numeric types
int, bigint, smallint and tinyint
Four integer types. The question is how big the numbers get.
tinyint, 1 byte, 0 to 255. Unsigned only, which surprises people. Good for small enumerations like a status code.smallint, 2 bytes, -32,768 to 32,767.int, 4 bytes, roughly plus or minus 2.1 billion.bigint, 8 bytes, about 9.2 quintillion either way.
I default to int for a surrogate key. Two billion rows is more than most tables will ever hold, and the four bytes saved over bigint get multiplied by every non-clustered index that carries the clustering key. Anything that logs events goes straight to bigint, because event tables grow at a rate nobody predicts and running out of identity values on a live table at 2.1 billion is a bad day. Skipping tinyint for status columns and using int everywhere is a defensible laziness. Using bigint for everything because storage is cheap is not, since it costs memory in the buffer pool on every page read too.
decimal against float
decimal(p, s) is exact. You state precision and scale, and the engine stores the digits. 0.1 + 0.2 equals 0.3. Precision goes up to 38 digits. Storage grows in steps: 5 bytes up to 9 digits, 9 bytes to 19, 13 bytes to 28 and 17 bytes to 38.
numeric is the same type under a different name.
float(n) is approximate binary floating point, IEEE 754. It cannot represent 0.1 exactly, in the same way decimal cannot represent one third. float(53) is 8 bytes and is what a bare float gives you. real is float(24) and 4 bytes.
DECLARE @dec decimal(19,4) = 0.1;
DECLARE @flt float = 0.1;
SELECT CASE WHEN @dec * 3 = 0.3 THEN 'exact' ELSE 'not exact' END AS decimal_check,
CASE WHEN @flt * 3 = 0.3 THEN 'exact' ELSE 'not exact' END AS float_check;decimal_check float_check
------------- -----------
exact not exactMoney goes in decimal. Always. Anything a customer sees on an invoice, anything that gets summed and reconciled, anything a regulator asks about. Float is for measurements whose input already carries error bars: sensor readings, latency samples, scientific data spanning fifteen orders of magnitude. I also avoid the money type. It looks purpose-built and has a well-known habit of losing precision in division, because it holds only four decimal places through intermediate results. decimal(19,4) does the same job with rules you can predict.
varchar against nvarchar
varchar(n) stores one byte per character in the column's collation, up to 8,000. nvarchar(n) stores UTF-16, two bytes per character, up to 4,000. Both have a max variant that spills out of the row and holds up to 2 GB. The fixed-length char and nchar pad to the declared length, and they earn their place only on codes of a fixed width, like a two-letter country code.
Storage is the obvious cost and the smaller one. A wider column means fewer rows per 8 KB page, which means more pages read for the same query, which shows up as buffer pool pressure long before it shows up as disk usage. An nvarchar(4000) column holding email addresses is a real tax on a big table.
Set against that, guessing wrong about encoding is expensive to undo. So: nvarchar for anything a human types, meaning names, addresses, free text and product descriptions. varchar for machine-generated identifiers you control, meaning SKUs, ISO codes, hashes and base64. Modern SQL Server also offers UTF-8 collations for char and varchar, which changes the arithmetic for mostly-ASCII text with occasional Unicode in it. I have not benchmarked those on a table large enough to say where the crossover sits, so treat that as a direction to test and not a recommendation. Check what your instance has:
SELECT name, description
FROM sys.fn_helpcollations()
WHERE name LIKE '%UTF8%';Collation rides along with this choice too, and the collation-sensitive behaviour of REPLACE and friends turns up in SQL Server string functions. String comparisons and sorting follow the column's collation and not the server's, so a case-sensitive join against a case-insensitive column does something other than what the code implies. Comparison behaviour is the other consequence, and it gets its own section below under precedence.
Date and time types
Six of them, and two are legacy:
date, 3 bytes, 0001-01-01 to 9999-12-31. Use it whenever there is no time component. A birth date is not a timestamp.time(n), 3 to 5 bytes depending on the fractional scale.datetime2(n), 6 to 8 bytes, scale 0 to 7. This is the default choice for a timestamp.datetimeoffset(n), 8 to 10 bytes, carries a UTC offset. Use it when the offset is part of the fact you are recording.datetime, 8 bytes, range starts at 1753, and it rounds to increments of .000, .003 and .007 seconds.smalldatetime, 4 bytes, minute precision, 1900 to 2079.
Pick datetime2 over datetime for new columns. It is more precise and its range is sane. At scale 0 to 2 it is smaller too. I use datetime2(0) for order timestamps and datetime2(3) where milliseconds carry meaning. Scale 7 is 100-nanosecond resolution and nothing in a business application needs it. One detail that follows from all this: GETDATE() returns datetime while SYSDATETIME() returns datetime2(7), so a column defaulted to GETDATE() caps its own precision at what datetime can hold. SQL Server date functions goes further into which function returns which type, and the choice matters more than it looks when you start comparing values across columns.
bit and uniqueidentifier
bit holds 0, 1 or NULL. SQL Server packs up to eight bit columns from the same row into a single byte, so a dozen flags on one table cost two bytes between them. Strings convert on assignment: 'TRUE' becomes 1, 'FALSE' becomes 0 and a string like 'yes' fails to convert. Converting any nonzero number gives 1. A nullable bit is three-state, which is occasionally what you want and more often a modelling mistake dressed up as a flag.
uniqueidentifier is 16 bytes holding a GUID. It solves a real problem, generating keys on disconnected clients without a round trip, and it costs four times the width of an int in every index. NEWID() as a default on a clustered primary key is the bigger issue. The values are random, so inserts land in the middle of the index. Page splits and fragmentation start in the first week. NEWSEQUENTIALID() exists for that case and only works as a column default. Where client-side generation is not needed, use an identity int and put the GUID in a separate indexed column for external references.
