Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
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
add comment

1 Answer

up vote 4 down vote accepted

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)
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.