Column size with datatype decimal

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 ?

-
show us exact SQL please – gbn Nov 14 '11 at 17:14
The cutoff point for `DATALENGTH` reporting 5 or 9 bytes in this instance seems to be `42949.67295` (stored as `01 FF FF FF FF 00 00 00 00`) and `42949.67296` (stored as `01 00 00 00 00 01 00 00 00`). Not sure how useful that behaviour is exactly! – Martin Smith Nov 14 '11 at 17:34
`CREATE TABLE [Test195]( [Date] [date] NULL, [Time] [time](3) NULL, [CCYPairID] [tinyint] NULL, [Price] [decimal](19, 5) NULL, [Amount] [bigint] NULL, )` `SELECT [Date] ,[Time] ,[CCYPairID] ,DATALENGTH([CCYPairID]) as CByte ,[Price] ,DATALENGTH([Price]) as PByte ,[Amount] ,DATALENGTH([Amount]) as AByte FROM [Test195]` – nojetlag Nov 14 '11 at 17:55

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)
``````
-
So you are saying that datalength() only gives me the bytes that are needed to display the given column but the actual size on disk will vary ? I did some tests with tinyint and money and there the amount of bytes did match what I would have expected according to the documentation. I'm asking these questions because I have around 13 Billion rows in a table and want to optimize the throughput even in the worst case of a full table scan. Would in this case the byte size from storage or the byte size for display matter ? (at the moment I need aprox. 15 min for a full table scan) – nojetlag Nov 14 '11 at 17:10
there is no optimisation for a full table scan. Don't focus on what an inappropriate function gives you: think of indexing or partitioning or archiving – gbn Nov 14 '11 at 18:30