Sql-server – Column size with datatype decimal

datatypessql serversql-server-2008

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:

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 ?
why is the information in BOL not matching with what datalength() returns ?

Best Answer

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)

Related Solutions

Handling Unexpected Results with Modulo Operator and Nonintegers

I will start with a link: What Every Programmer Should Know About Floating-Point Arithmetic.

In short, float-point arithmetic types, like the float and double mysql types should never be used for precise arithmetic. And your col mod 0.1 is trying to do exactly that, a precise arithmetic check. It is trying to find if the value in col is an exact multiple of 0.1. And it fails miserably, as it is obvious from your results.

The solution is to use a fixed type, like decimal(m, n) to do these types of checks.

As for why you got different results in Oracle, it's because you used NUMBER which is implemented differently (with decimal precision). The equivalents in Oracle of float and double are BINARY_FLOAT and BINARY_DOUBLE and use binary precision. See the notes in Oracle docs: Overview of Numeric Datatypes:

Oracle Database provides two numeric datatypes exclusively for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE. They support all of the basic functionality provided by the NUMBER datatype. However, while NUMBER uses decimal precision, BINARY_FLOAT and BINARY_DOUBLE use binary precision. This enables faster arithmetic calculations and usually reduces storage requirements.

Note:
BINARY_DOUBLE and BINARY_FLOAT implement most of the Institute of Electrical and Electronics Engineers (IEEE) Standard for Binary Floating-Point Arithmetic, IEEE Standard 754-1985 (IEEE754).

If you insist to use double (I see no reason why), a few more tests with the mod operator/function in mysql, reveals that:

When you use integers for the arguments, say you do x mod K, the result is always an integer between 0 and K-1. The expected mathematical behaviour.

When you use floats/doubles, say you do x mod K, the result is any number from 0.0 to K included. (as your tests which show 5850.0 mod 0.1 to result in 0.1). I haven't done millions of tests, so there may be cases where the result can even be larger than K! Floating-point arithmetic can give weird results.

So, a workaround (which may not always work!) would be to use this condition:

( mod(col, 0.1) <> 0.0  AND  mod(col, 0.1) <> 0.1 )

To be honest, I feel sad I even made the workaround suggestion. Please read again the What Every Programmer Should Know About Floating-Point Arithmetic and don't use the workaround.

Use fixed-precision types for precision arithmetic operations.

SQL Server – Round-Tripping Column Datatype Causes Table Size to Grow

When changing the data type of a column, SQL Server will choose to either:

Change the metadata only;
Change the metadata and read all the existing values to ensure they fit; or
Change every row physically

Even if every row must be changed, SQL Server still take steps (where possible) to prioritize speed over final size, on the basis that we want DDL changes to complete as quickly as possible. Optimizing the storage space can wait for a maintenance window.

Changing float to smallint can be accommodated within the existing space allocated for the row, but it does leave some unused space. As has been mentioned, this can be reclaimed by fully rebuilding the changed structure.

Best Answer

Related Solutions

Handling Unexpected Results with Modulo Operator and Nonintegers

SQL Server – Round-Tripping Column Datatype Causes Table Size to Grow

Related Question