Use and difference of Decimal (numeric), money, and float of MSSQL

Decimal (numeric), money, and float (real) are all floating point data types in MSSQL.

Sort by storage range

Float (real)

Decimal (numeric)

Money

 

You can give priority to money on the amount of storage. If the amount is too large, use decimal (numeric). In extreme cases (data is too large), use float (real)

 

Money and float do not automatically default the number of decimal places, and the floating point value inserted automatically defaults.

For example:

Create Table #
(
Price float
)

Drop table #

Insert into #
Select 1, 100.1245678
Union all
Select 1, 101.123
Union all
Select 1, 102.12
Union all
Select 1, 103.1
Union all
Select 1, 103

Select * from #

 

The decimal (numeric) type has a high precision. You can specify the number of decimal places and customize them.

Create Table #
(
Price decimal (10, 2)
)

Drop table #

Insert into #
Select 1, 100.1245678
Union all
Select 1, 101.123
Union all
Select 1, 102.12
Union all
Select 1, 103.1
Union all
Select 1, 103

Select * from #

 

Float precision distortion example:

Create Table Q (

Money float (15,3)

);

 

Insert the data:

Insert into Q values (1234567.234 );

 

After reading the code, we can see that it displays:

1234567.250

 

Insert the data:

Insert into Q values (12345672.34 );

 

After reading the code, we can see that it displays:

12345672.000

That is to say, if there are more than 10 digits, there will be inaccuracy.

 

As can be concluded, the smaller the value storage range, the higher the precision. The larger the storage range, the less accurate the precision. If the normal amount is stored, the use of money, the advantage is that you can store numbers that do not specify the number of decimal places. If decimal (numeric) is used for numerical storage that requires both precision and fixed decimal digits, the advantage is that the number of decimal digits can be customized with high precision. In special cases, for example, the float (real) type can only be used for a large value range. This type is generally not recommended.