1 is not equal to 1? Differences between numeric, decimal, float, and real data types

You have not met in SQL 1 is not equal to 1 (1<>1) situation!? Here's an example to illustrate the situation.

Let's start with a brief introduction to the four numeric data types in the header.

In T-SQL, numeric and decimal are exact numeric data types, while float and real are approximate numeric data types.

Numeric and decimal are the same. Real is the same as float (24).

When defining a float (n) Type column, if the value of n is explicitly specified, for example

float (ten)

Then when the value of n is between 1-24, the system marks the column (val) as the real type, and when the value of n is between 25-53, the system marks the column as a float type. Validation can be obtained from the system table sys.columns.

If not explicitly specified, the default value for N is 53. To add, because the value of the float type is represented by the scientific notation, n represents the number of significant digits of the value, and MSDN describes N as the number of digits in the mantissa.

Now look at the specific example.

1 CREATE TABLETmain (2IdINT IDENTITY(1,1)PRIMARY KEY,3ValFLOAT4 )5 GO6 7 CREATE TABLETdetail (8IdINT IDENTITY(1,1)PRIMARY KEY,9PidINT,TenValFLOAT One ) A GO -  - INSERT  intoTmainVALUES( -); the INSERT  intoTmainVALUES( -); -  - INSERT  intoTdetailVALUES(1, -); - INSERT  intoTdetailVALUES(1, *); + INSERT  intoTdetailVALUES(1, *); -  + INSERT  intoTdetailVALUES(2, -); A INSERT  intoTdetailVALUES(2, -); at INSERT  intoTdetailVALUES(2, +); -  - --SELECT * from Tmain; - --SELECT * from Tdetail; -  - SELECTTm.id,SUM(Tm.val*Td.val)/ 10000.0  asSumper in  fromTmain TMINNER JOINTdetail TD onTm.id=Td.pid - GROUP  bytm.id to  + SELECTTm.id,SUM(Tm.val*Td.val)/ 10000.0  asSumper -  fromTmain TMINNER JOINTdetail TD onTm.id=Td.pid the GROUP  bytm.id *  $ SELECT * Panax Notoginseng  from ( -     SELECTTm.id,SUM(Tm.val*Td.val/ 10000.0) asSumper the      fromTmain TMINNER JOINTdetail TD onTm.id=Td.pid +     GROUP  bytm.id A ) TMS the WHERETms. Sumper= 1; +  - SELECT *  $  from ( $     SELECTTm.id,SUM(Tm.val*Td.val)/ 10000.0  asSumper -      fromTmain TMINNER JOINTdetail TD onTm.id=Td.pid -     GROUP  bytm.id the ) TMS - WHERETms. Sumper= 1;Wuyi  the DROP TABLETmain; - DROP TABLETdetail;

Do you want to predict the outcome before you do it?

Take a look at the results of the first two SELECT statements.

First select

Id	Sumper
1	1
2	1

A second Select

Id	Sumper
1	1
2	1

The result is the same, at least, looks the same!

Take a look at the results of the subsequent two SELECT statements.

A third Select

Id	Sumper
2	1

Fourth a Select

Id	Sumper
1	1
2	1

The results of the latter two select produce differences: One is first multiplication, then plus minus, the other is first plus minus, then multiplication.

Because the Val column is a float type and is an approximate numeric data type, it is clear that the result of the first select has a row of data that produces an approximate value of 1 during the calculation. The 1 specified in the WHERE statement, by default, is the int type, and the int type is the exact data type. So the third SELECT statement has a 1<>1 situation.

Examples are complete.

If you define a val column as a decimal type, the result of the above four SELECT statements is the same. It is recommended to use approximate numeric data types with caution, and to anticipate potential problems when calculating and comparing values.

1 is not equal to 1? Differences between numeric, decimal, float, and real data types