Column size with datatype decimal

Question

According to BOL for SQL Server 2008 R2 the data type decimal requires the following storage bytes:

Precision  Storage bytes

1 - 9      =>   5

10-19      => 9

20-28      => 13

29-38      => 17

However, when I do a datalength() on a column that is formatted decimal(19,5) and has the value of 10999.99999 I get back 5 bytes ? Which is according to my understanding a precision of 10 and not of 9 and should result in 9 bytes.

this results in two questions:

1. is the size of the table with this column not depending on the column definition, instead the true values within the column define the table size ?

2. why is the information in BOL not matching with what datalength() returns ?

Answer 1

Don't mix storage (5 or 9 bytes) and what you get back

In a decimal(19,5) column it is always 9 bytes on disk. So zero will take nine bytes.

Your 10999.99999 is merely a representation of that stored number that is then parsed as 10999.99999 by DATALENGTH

Edit:

DATALENGTH will return the number of bytes needed to store the expression given. It ignores datatype mostly. So 10999.99999 is treated as decimal(10,5) which can be stored in 5 bytes not 9 as Martin pointed out in a comment.

So a decimal(19,5) column is always 9 bytes. DATALENGTH doesn't care. It looks at the expression 10999.99999 and decides it can be stored in 5 bytes. But of course it can't

Personally, I've never used DATALENGTH except to find trailing spaces or such.

Basically, don't use DATALENGTH on non string datatypes.

Returns the number of bytes used to represent any expression.

DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.

Note: LEN tells you how long the string representation is

DECLARE @i decimal(19,5)
SET @i ='10999.99999'
SELECT @i,LEN(@i),DATALENGTH(@i)
SET @i ='-90999.99999'
SELECT @i,LEN(@i),DATALENGTH(@i)