Interview Question of the Week #058 – What is the Difference Among DECIMAL, FLOAT and NUMERIC Datatype?

Question: What is the difference among DECIMAL, NUMERIC and FLOAT? In SQL Server, DECIMAL and NUMERIC are synonyms for the same exact fixed-precision type. FLOAT is an approximate binary floating-point type.

The definition is a common interview question. I would follow it with a value: where will rounding occur, and must this value be represented exactly? That turns the vocabulary into a design decision.

DECLARE @D decimal(4,2)=11.22,@N numeric(6,4)=11.2222;
SELECT @D AS DecimalValue,@N AS NumericValue,
       SQL_VARIANT_PROPERTY(@D,'Precision') AS DecimalPrecision,
       SQL_VARIANT_PROPERTY(@D,'Scale') AS DecimalScale;
DECLARE @Exact decimal(3,2)=0.1,@Approx float=0.1;
SELECT CONVERT(decimal(38,20),@Exact) AS ExactTenth,
       CONVERT(decimal(38,20),@Approx) AS ApproximateTenth;
DECLARE @Small float(6)=11.2222,@Wide float(53)=11.2222;
SELECT DATALENGTH(@Small) AS Float6Bytes,DATALENGTH(@Wide) AS Float53Bytes;

Precision and scale need valid declarations

Precision is the total decimal digits; scale is the digits after the decimal point. DECIMAL(4,2) can store 11.22, and NUMERIC(6,4) can store 11.2222. They are different precision/scale declarations of the same type family.

The original DECIMAL(2,4) declaration was invalid because scale cannot exceed precision. DECIMAL(2,2) also cannot hold 11.22: it has no digits available before the decimal point. These are not merely two different spellings for that example.

Exact has boundaries too

DECIMAL represents values exactly within its declared precision and scale. It can round when converted to a smaller scale and overflow when the integer part does not fit. Arithmetic result types and conversion order matter; exact storage is not unlimited precision.

The second result renders the stored tenth with 20 decimal places. The decimal value is exact at its scale; the FLOAT value exposes its nearby binary approximation. The extra displayed digits are a diagnostic illustration, not a claim that FLOAT has 20 accurate decimal digits.

FLOAT’s argument is binary precision

FLOAT(6) does not mean six digits after the decimal point. SQL Server treats declarations 1 through 24 as the four-byte, approximately seven-decimal-digit form, and 25 through 53 as the eight-byte, approximately fifteen-digit form. The default is 53.

Choose a suitable DECIMAL precision and scale when exact monetary or business quantities matter. FLOAT can suit measurements requiring a large range where an approximation is acceptable. Avoid equality assumptions or a blanket claim that FLOAT is faster without measuring the actual workload.

You can connect with me on LinkedIn.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.