Why is datalength of decimal 5 regardless of precision?

Question

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?

Answer 1

I think you will find quite a bit of interesting information about this topic in Reducing Database Size by Using Vardecimal Storage Format, but to answer your question (quoting from the post, highlighting mine):

The storage size depends on both the declared precision of the column AND the value. For example, if you have a decimal column declared with precision 18 and the common value for this column has only 3 digits, SQL Server uses only 5 bytes, which is almost 50% less than the 9 bytes used by the fixed format. But, if the common value for the column has 18 digits, SQL Server uses 11 bytes, which is 20% more than the corresponding size in the fixed-length format.

Try inserting values with larger precision and your results will change.

On another note:

You can enable Vardecimal storage at the database and table level. Using SSMS Object Explorer, you can look at your database options and table storage properties to verify whether you have Vardecimal storage enabled. You can also use sp_db_vardecimal_storage_format, which notes:

Returns the current vardecimal storage format state of a database or enables a database for vardecimal storage format. Starting with SQL Server 2008, user databases are always enabled. Enabling databases for the vardecimal storage format is only necessary in SQL Server 2005.

That said, Enterprise-only vardecimal storage was deprecated in SQL Server 2008 in favour of row compression, which is a superset of vardecimal storage capabilities. Starting with SQL Server 2016 SP1, row and page data compression are available in all editions, and do not require a database configuration option to use.

Answer 2

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 |
+------------------------------------------+--------------------------------------------+------------------+-----------------+