Why is datalength of decimal 5 regardless of precision? I'm trying to better understand numeric types in SQL and have read that the decimal type will always require 17 bytes. However, the MS Docs list a table indicating the amount of space used depends on the decimal's precision. So I tried to test it using the datalength function.

create table tbl_TestDec(dec1 decimal(19,4), dec2 decimal(20,4), dec3 decimal(9,4))

insert into tbl_TestDec
select 1, 1, 1

select datalength(dec1), datalength(dec2), datalength(dec3) from tbl_TestDec


This outputs:

5   5   5


I was expecting either 9  13  5 or 17  17  17. I'm using SQL Server 2005. Are all decimals vardecimal or am I misunderstanding the datalength function?
DATALENGTH returns the size (in bytes) of the internal representation of the value passed to it.

It does not return the storage size required (which might depend on where you store it) nor does it return the maximum size of data that might be stored in the type.

For example (dbfiddle demo):

SELECT
    N.d, 
    AsBinary = CONVERT(varbinary(20), N.d),
    DataLengthVarBin = DATALENGTH(CONVERT(varbinary(20), N.d)),
    DataLengthDec38 = DATALENGTH(N.d) 
FROM 
(
    SELECT 
        CONVERT(
            decimal(38, 38), 
            '0.' + 
            REPLICATE('0', 38 - SV.number) + 
            REPLICATE('1', SV.number))
    FROM master.dbo.spt_values AS SV
    WHERE [SV].[type] = N'P'
    AND SV.number BETWEEN 1 AND 38
) AS N (d);


gives:


+------------------------------------------+--------------------------------------------+------------------+-----------------+
|                    d                     |                  AsBinary                  | DataLengthVarBin | DataLengthDec38 |
+------------------------------------------+--------------------------------------------+------------------+-----------------+
| 0.00000000000000000000000000000000000001 | 0x2626000101000000                         |                8 |               5 |
| 0.00000000000000000000000000000000000011 | 0x262600010B000000                         |                8 |               5 |
| 0.00000000000000000000000000000000000111 | 0x262600016F000000                         |                8 |               5 |
| 0.00000000000000000000000000000000001111 | 0x2626000157040000                         |                8 |               5 |
| 0.00000000000000000000000000000000011111 | 0x26260001672B0000                         |                8 |               5 |
| 0.00000000000000000000000000000000111111 | 0x2626000107B20100                         |                8 |               5 |
| 0.00000000000000000000000000000001111111 | 0x2626000147F41000                         |                8 |               5 |
| 0.00000000000000000000000000000011111111 | 0x26260001C78AA900                         |                8 |               5 |
| 0.00000000000000000000000000000111111111 | 0x26260001C76B9F06                         |                8 |               5 |
| 0.00000000000000000000000000001111111111 | 0x26260001C7353A42                         |                8 |               5 |
| 0.00000000000000000000000000011111111111 | 0x26260001C719469602000000                 |               12 |               9 |
| 0.00000000000000000000000000111111111111 | 0x26260001C701BDDE19000000                 |               12 |               9 |
| 0.00000000000000000000000001111111111111 | 0x26260001C71162B302010000                 |               12 |               9 |
| 0.00000000000000000000000011111111111111 | 0x26260001C7B1D4011B0A0000                 |               12 |               9 |
| 0.00000000000000000000000111111111111111 | 0x26260001C7F14E120E650000                 |               12 |               9 |
| 0.00000000000000000000001111111111111111 | 0x26260001C77115B78CF20300                 |               12 |               9 |
| 0.00000000000000000000011111111111111111 | 0x26260001C771D6267F792700                 |               12 |               9 |
| 0.00000000000000000000111111111111111111 | 0x26260001C7716084F7BE8A01                 |               12 |               9 |
| 0.00000000000000000001111111111111111111 | 0x26260001C771C42BAB756B0F                 |               12 |               9 |
| 0.00000000000000000011111111111111111111 | 0x26260001C771ACB5AF98329A                 |               12 |               9 |
| 0.00000000000000000111111111111111111111 | 0x26260001C771BC18DDF6F90506000000         |               16 |              13 |
| 0.00000000000000001111111111111111111111 | 0x26260001C7715CF7A2A4C33B3C000000         |               16 |              13 |
| 0.00000000000000011111111111111111111111 | 0x26260001C7719CA95D6EA4555A020000         |               16 |              13 |
| 0.00000000000000111111111111111111111111 | 0x26260001C7711CA0A84F6C5887170000         |               16 |              13 |
| 0.00000000000001111111111111111111111111 | 0x26260001C7711C41961C3B7449EB0000         |               16 |              13 |
| 0.00000000000011111111111111111111111111 | 0x26260001C7711C8BDE1D4F8ADE300900         |               16 |              13 |
| 0.00000000000111111111111111111111111111 | 0x26260001C7711C6FB12A1767B1E85B00         |               16 |              13 |
| 0.00000000001111111111111111111111111111 | 0x26260001C7711C57EEAAE706EE169703         |               16 |              13 |
| 0.00000000011111111111111111111111111111 | 0x26260001C7711C674FAD0C454CE5E623         |               16 |              13 |
| 0.00000000111111111111111111111111111111 | 0x26260001C7711C071AC57EB2FAF4046701000000 |               20 |              17 |
| 0.00000001111111111111111111111111111111 | 0x26260001C7711C4704B3F3F8CA9131060E000000 |               20 |              17 |
| 0.00000011111111111111111111111111111111 | 0x26260001C7711CC72AFE84B9EDB1EF3D8C000000 |               20 |              17 |
| 0.00000111111111111111111111111111111111 | 0x26260001C7711CC7ABED313F49F35C6B7A050000 |               20 |              17 |
| 0.00001111111111111111111111111111111111 | 0x26260001C7711CC7B548F377DC80A131C8360000 |               20 |              17 |
| 0.00011111111111111111111111111111111111 | 0x26260001C7711CC719D780AF9C084FF0D1230200 |               20 |              17 |
| 0.00111111111111111111111111111111111111 | 0x26260001C7711CC7016708DB1E56166333661500 |               20 |              17 |
| 0.01111111111111111111111111111111111111 | 0x26260001C7711CC71106548E345DDFDE01FED500 |               20 |              17 |
| 0.11111111111111111111111111111111111111 | 0x26260001C7711CC7B13C488F0DA4B9B412EC5B08 |               20 |              17 |
+------------------------------------------+--------------------------------------------+------------------+-----------------+