RSS Feed

Difference between Decimal and Float

Difference between Decimal and Float


The main difference between the decimal and float data types is that you have a more precise level of storage with decimal than float. Lets look at few more differences between decimal and float with examples.

Decimal Float
Decimal data type stores numeric data with fixed precision and scale. The precision determines the number of decimals that are stored after the decimal point and the scale determines the total number of digits that can be used. Float data type stores numeric data with floating decimal precision.
decimal[(p[, s])]

p (precision)
Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The maximum precision is 38. The default precision is 18.

s (scale)
Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p.		 The Microsoft® SQL Server™ float[(n)] data type conforms to the SQL-92 standard for all values of n from 1 to 53. The synonym for double precision is float(53).
CREATE TABLE tab_3
(
Num_Decimal decimal (39,39)
);

Result:
Msg 2750, Level 16, State 1, Line 1
Column or parameter #1: Specified column precision 39 is greater than the maximum precision of 38. 		CREATE TABLE tab_2
(
Num_Float Float (54)
);

Result:
Msg 2750, Level 16, State 1, Line 1
Column or parameter #1: Specified column precision 54 is greater than the maximum precision of 53.
Select cast (3.55 as decimal)

Result:
(No column name)
4		 Select cast (3.55 as float)

Result:
(No column name)
3.55
CREATE TABLE tab_11
(
Num_Decimal decimal
);

Insert into tab_11 values (3.123456789);

Select * from tab_11;

Result:
Num_Decimal
3
 CREATE TABLE tab_12
(
Num_Float Float
);

Insert into tab_12 values (3.123456789);

Select * from tab_12;

Result:
Num_Float
3.123456789
CREATE TABLE tab_4
(
Num_Decimal decimal (38,0)
);

Insert into tab_4 values (12345678912345678123456789123456787999);

Select * from tab_4;

Result:
Num_Decimal
12345678912345678123456789123456787999 		CREATE TABLE tab_5
(
Num_Float float (53)
);

Insert into tab_5 values (12345678912345678123456789123456787999);

Select * from tab_5;

Result:
Num_Float
1.23456789123457E+37


Also See:
Microsoft SQL Server 2012 Step by Step (Patrick LeBlanc)
MCSA Guide to Microsoft SQL Server 2012 (Exam 70-462) (Faisal Akkawi, Kayed Akkawi, Gabriel J. Schofield)