Wednesday, January 29, 2014

Note on precision & scale in SQL Server

Generally for storing a variable like 123.45 we can use a decimal or numeric data type. The usual notation for these data types are decimal [ (p[ ,s] )] & numeric[ (p[ ,s] )], p stands for precision & s stands for scale.

Precision is number digits in variable
Scale is of digits to the right of the decimal point
For ex: 12.345 p = 5 & s = 3

The sample query explains the behavior of the decimal, float & int

declare @t table(
     c1 int,
     c2 float,
     c3 decimal(18,2)
)


insert into @t (c1,c2,c3) select 12.345, 12.345, 12.345
insert into @t (c1,c2,c3) select 10/3, 10/3, 10/3
insert into @t (c1,c2,c3) select 10/3.0, 10/3.0, 10/3.0
insert into @t (c1,c2,c3) select 10/3.0, 10/3.0, round(10/3.0, 2)


select * from @t


c1        c2                    c3
12        12.345                12.35
Note: The value in C3 is truncated to two decimal places, C1 completely ignored the fraction part.

3           3            3.00
Note: The fraction part is completely ignored because the denominator is an integer

3           3.333333           3.33
Note: The fraction part is retained correctly

3           3.333333           3.33
Note: The fraction part can also be achieved by rounding the value using round function