SQL Server’s MONEY data type is where you should be looking first if you are storing something like prices and order totals. But what about pesky GST and VAT values? These sometimes need to extend out to 6, 8 or even more decimal places in order to calculate the correct tax inclusive price.

Along comes the DECIMAL data type. It’s configurable in that you get to choose how many digits maximum are in your number, and how many of those are after the decimal place.

From SQL Server Books Online “Money Data Type, Overview”:

Monetary data can be stored to an accuracy of four decimal places.
…
If a greater number of decimal places are required, use the decimal data type instead.

DECIMAL is an acceptable substituate for the MONEY / SMALLMONEY data types, as it represents an exact number. DECIMAL is synonymous with the NUMERIC and DEC datatypes.

The DECIMAL data type is defined as:

DECIMAL(precision,scale)
Precision is the total number of digits allowed in the number, both left and right of the decimal point.
Scale is the maximum number of digits allowed to the right of the decimal point. (The number of decimal places).

NEVER USE FLOAT OR REAL DATATYPES FOR MONETARY VALUES! Both FLOAT and REAL are approximate number data types. The number you put in, and the number you get out will almost never be the same.

Here are some example of how DECIMAL can be used:

-- 4 decimal places
SELECT CAST(1.1234567890123456789 AS DECIMAL(22,4))

------------------------ 
1.1235



-- 8 decimal places
SELECT CAST(1.1234567890123456789 AS DECIMAL(22,8))

------------------------ 
1.12345679



-- 12 decimal places
SELECT CAST(1.1234567890123456789 AS DECIMAL(22,12))

------------------------ 
1.123456789012
Tags: