How SQL Server Stores DECIMAL and NUMERIC, Down to the Bytes
Every schema has one: a column declared decimal(38, 4) because someone, at some point, wanted to be safe. Nobody remembers the number that made 38 digits feel necessary, and nobody has dared to shrink it since. The instinct is understandable, but it is not free, and the cost is easiest to see once you know exactly what SQL Server writes to the page when you store a decimal. This post takes a decimal apart on disk, shows the sign-plus-integer layout it actually uses, and explains why precision buys storage in tiers rather than by the digit.
This continues the byte-level storage series alongside strings and UTF-8, the uniqueidentifier, and where each column lives in a record. If you have not watched a row get pulled apart with DBCC PAGE, this older post covers the mechanics. Every byte below is from a SQL Server 2019 instance, and the scripts are included so you can reproduce them.
decimal and numeric are the same type
Start with the least interesting fact, because it clears the decks: decimal and numeric are synonyms.[1] They compile to the same thing, store the same bytes, and there is no performance or storage reason to prefer one keyword over the other. Pick one for consistency and move on. Everything in this post applies equally to both.
A decimal(p, s) is defined by two numbers. Precision p is the total count of significant digits, from 1 to 38. Scale s is how many of those digits sit to the right of the decimal point. So decimal(9, 2) holds up to nine digits, two of them after the point: values from -9999999.99 to 9999999.99. The default when you write bare decimal is decimal(18, 0), which surprises people who expected decimals to keep a fractional part.
Precision buys bytes in tiers
Here is the first thing worth designing around: storage size does not grow one byte per digit. It steps up in four tiers, and every precision inside a tier costs exactly the same.[1]
| Precision | Storage bytes |
|---|---|
| 1 – 9 | 5 |
| 10 – 19 | 9 |
| 20 – 28 | 13 |
| 29 – 38 | 17 |
You can confirm it from the catalog. Four columns, one in each tier:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE #sizes ( [d09] decimal(9, 2) NOT NULL , [d19] decimal(19, 4) NOT NULL , [d28] decimal(28, 6) NOT NULL , [d38] decimal(38, 0) NOT NULL ); SELECT [column_name] = [c].[name] , [max_length] = [c].[max_length] , [precision] = [c].[precision] , [scale] = [c].[scale] FROM [tempdb].[sys].[columns] AS [c] WHERE [c].[object_id] = OBJECT_ID(N'tempdb..#sizes', N'U') ORDER BY [c].[column_id]; |
|
1 2 3 4 5 6 |
column_name max_length precision scale ----------- ---------- --------- ----- d09 5 9 2 d19 9 19 4 d28 13 28 6 d38 17 38 0 |
The practical consequence is that the decimal(38, 4) from the introduction costs 17 bytes per row, while a decimal(9, 4) that covers everything up to about 99,999 costs 5 bytes. On a narrow table with a few million rows, that difference is real, and it repeats in every nonclustered index that carries the column. Scale, notice, does not appear in the size at all: decimal(9, 0) and decimal(9, 8) both cost 5 bytes. Scale changes where the decimal point sits, not how much you store.
The on-disk layout: a sign byte and an integer
Now the part that explains the tiers. SQL Server does not store the digits as text, and it does not store the decimal point at all. It stores the value as an integer, scaled up by 10^s so the fractional part becomes whole, preceded by a single byte for the sign. The point’s position is metadata, fixed by the column’s scale, so it never needs to travel with the data.
Let us prove it. A tiny table, four values in one decimal(9, 2) column, then a raw page dump:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE [dbo].[dec_demo] ( [label] varchar(20) NOT NULL , [val] decimal(9, 2) NOT NULL ); INSERT INTO [dbo].[dec_demo] ( [label], [val] ) VALUES ( N'123.45', 123.45 ) , ( N'-123.45', -123.45 ) , ( N'1.00', 1.00 ) , ( N'0.00', 0.00 ); |
Reading the four records off the page with DBCC PAGE, the five bytes of each decimal(9, 2) value are:
|
1 2 3 4 5 6 |
value on-disk bytes sign little-endian integer ------- --------------- ---- --------------------- 123.45 01 39 30 00 00 01 0x00003039 = 12345 -123.45 00 39 30 00 00 00 0x00003039 = 12345 1.00 01 64 00 00 00 01 0x00000064 = 100 0.00 01 00 00 00 00 01 0x00000000 = 0 |
Read the first row. The value 123.45 at scale 2 becomes the integer 123.45 * 100 = 12345. In hexadecimal that is 0x3039, and SQL Server writes it little-endian (least significant byte first), so it lands on the page as 39 30, zero-padded out to the tier’s four-byte mantissa: 39 30 00 00. In front of it sits the sign byte, 01 for positive.
The -123.45 row is the same four mantissa bytes, 39 30 00 00, with the sign byte flipped to 00 for negative. The magnitude is stored identically; only that leading byte carries the sign. 1.00 scales to the integer 100 (0x64), and 0.00 is a positive zero. That leading 01/00 sign byte is why a five-byte tier holds a four-byte integer: one of the five bytes is spent on the sign. The same logic gives the other tiers a 8-, 12-, or 16-byte integer behind their sign byte, which is enough to hold 19, 28, and 38 decimal digits respectively.
Because the stored value is an exact integer, decimal arithmetic is exact. This is the real reason to reach for decimal over float for money and other quantities where a rounding error is a bug rather than a rounding: there is no binary-fraction approximation happening, just an integer with an agreed-upon point.
Precision you never use still costs bytes
Because size is fixed by the declared precision, not by the value, an oversized column pays its tax on every single row regardless of what you put in it. The value 5 stored in decimal(5, 0) and in decimal(38, 0) is the identical number, but not the identical footprint:
|
1 2 3 |
SELECT [tiny_precision_bytes] = DATALENGTH(CONVERT(decimal(5, 0), 5)) , [huge_precision_bytes] = DATALENGTH(CONVERT(decimal(38, 0), 5)); |
|
1 2 3 |
tiny_precision_bytes huge_precision_bytes -------------------- -------------------- 5 17 |
Same value, more than three times the storage, purely because of a declaration. This is the concrete cost behind the decimal(38, 4)-to-be-safe habit.
How to pick precision and scale
None of this makes decimal a type to avoid; it is the correct choice for exact fixed-point quantities, and the tiers are coarse enough that right-sizing is easy. A few guidelines that fall out of the layout:
- Size precision to the real domain, then stop. Count the digits the value can actually reach, add a little headroom, and pick the smallest precision that covers it. Landing at precision 9 instead of 19 halves the storage, from 9 bytes to 5.
- Mind the tier boundaries. The jumps are at precision 10, 20, and 29. Every precision inside a tier costs the same, so if you need 20 digits there is no storage saving in trimming to 22; both sit in the 13-byte tier. The savings come from staying under a boundary (9 rather than 10, or 19 rather than 20), not from shaving digits within a tier.
- Scale is free, within your chosen precision. Because scale does not affect size, choose the scale your data needs without worrying about bytes. Just remember scale counts against precision:
decimal(9, 4)leaves only five digits for the integer part. - Reach for decimal, not float, when exactness matters. Money, tax rates, and measured quantities that must add up want the exact-integer storage
decimalprovides.floatandrealare for scientific magnitudes where the binary approximation is acceptable and the range is huge.
The decimal(38, 4) in your schema might be exactly right. But now it is a decision you can defend from the bytes up, rather than a number nobody remembers choosing.
What is the widest decimal you have found in a schema you inherited, and did anyone know why? I would like to hear it. Find me on Bluesky or LinkedIn.
References
- decimal and numeric (Transact-SQL) – Microsoft Learn. decimal and numeric are synonyms, precision 1 to 38, and the storage-bytes-by-precision table. ↩