Problems found when using the float type in sqlserver

In the project, the float type is used to define some columns, such as price, but many problems are found.
1. When the number of bits on the value is greater than 6, the float type is converted to the varchar type, which is displayed in the scientific and technical method.
In this case, you have to convert the float type to the numeric type, and then convert it to the varchar type.
2. When a float variable is stored in a value, its value sometimes changes. This phenomenon occurs when the quotation is saved, for example, a 3.8 record is saved, but it becomes 3.80001124 or 3.79998999 in the database.
In sqlserver's help, the float type is described as follows: the approximate numeric data type used to represent floating point numeric data. Floating point data is an approximate value; not all data within the data type range can be accurately expressed.
Therefore, this type must be used with caution in the future.

 
Comment

#218.69.6. * William Wang [unregistered user]It is better to use decimal (numeric ).         

How can I set the number of decimal places for the float type?

On the client (C #), If you store a decimal point, such as insert into (fielda) values (2.4), where fielda is of the float type. after being stored in the database, the data may change to 2.400000, 2.4000001, or 2. 399999999. dizzy.

How can I set it. ========================================================== ======================================== this does not matter. convert the display.

Select cast (fielda as decimal (18, 1 )) ========================================================== ==================================== decimal ====== ========================================================== =
========================================================== ======================================         

The float type in SQL Server 2000 displays four decimal places 

In SQL Server 2000:

If yesData Type Float11.8, it is stored as 11.800000000000001 by default. It looks quite uncomfortable when it is displayed. I only want four decimal places and found a lot of information, you can use the smallmoney model to implement the float model.

Floating Point Data TypeUsed to store decimal places.Floating Point DataInSQL ServerUsing the round-up (round up or inbound only) method for storage. When (and only if) the number to be rounded is a non-zero number, add 1 to the value in the lowest valid bit of the reserved number, and carry out the necessary carry. If a number is rounded up, its absolute value is not reduced. For example, round 3.14159265358979 by two or 12 digits, respectively. The result is 3.15 and 3.141592653590.

Currency dataIndicates a positive or negative currency value. In Microsoft SQL Server 2000, the money and smallmoney data types are used to store currency data. The precision of currency data storage is four decimal places. (This is exactly what I want to do: It's okay to show four decimal places)

Monetary data represents positive or negative amounts of money. in Microsoft SQL Server 2000, monetary data is stored usingMoneyAndSmallmoneyData Types. Monetary data can be storedAn accuracy of four decimal places.UseSmallmoneyData Type to store values in the range from-214,748.3648 through 214,748.3647 (requires 4 bytes to store a value ).

             

In SQL Server, how does one convert the float type to the varchar type?

The method is as follows:

Float to varchar type

Select convert (varchar (8000), cast (convert (float, '000000') as decimal ))

Select STR (convert (float, '20140901'), Len (convert (float, '20140901 ')))

Select convert (varchar (8000), cast (convert (float, '000000') as decimal ))

STR (float_expression [, length [,])

Remarks
If the length and decimal parameter values are provided for STR, these values should be positive numbers. When the default or decimal parameter is 0, the number is rounded to an integer. The specified length should be greater than or equal to the length of the part before the decimal point plus a numeric symbol (if any. The short float_expression is right aligned within the specified length, and the long float_expression is truncated to the specified decimal place. For example, the output result of STR () is 12. It is right aligned in the result set. STR (1223,2) truncates the result set **. Nested string functions are supported.

Note:
To convert Unicode data, use str in the convert or cast conversion function.

 

Parameters
Float_expression
An expression of the approximate number (float) data type with a decimal point.

Length
Total length. It includes the decimal point, symbol, number, and space. The default value is 10.

Decimal
The number of digits after the decimal point. Decimal must be less than or equal to 16. If the decimal value is greater than 16, the result is truncated to keep it as 16 digits after the decimal point.

Return type
Char

          

 SQL Server 2000 decimal type-use decimal, float, and real data

A decimal data type can store up to 38 numbers. All numbers can be placed on the right of the decimal point. A definite numeric expression is stored for the decimal data type; no approximate values are stored.

Two features of defining decimal columns, variables, and parameters are as follows:

• P

  Specifies the number of numbers that can be controlled by precision or object.

• S

  Specify the number of decimal places or numbers to the right of the decimal point.

  P and S must comply with the following rules: 0 <= S <= P <= 38.

The default maximum precision of numeric and decimal data types is 38. In Transact-SQL, numeric and decimal data types are functionally equivalent.

When data values must be precisely stored as specified, decimal data types with decimal numbers can be used to store numbers.

For information about how mathematical operations affect the accuracy and decimal places of results, see precision, decimal places, and length.

Use float and real data

Float and real data types are called similar data types. Float and real data follow the IEEE 754 standard in terms of approximate digital data types.

The approximate numeric data type does not store the exact values specified for most numbers. They only store the approximate values of these values. In many applicationsProgramThe small difference between the specified value and the stored value is not obvious. However, sometimes these differences are worth noting. Because of this approximation of float and real data types, when precise numeric States are required, such as in financial applications, in operations that require rounding, or, these data types are not used in the equivalence check operation. The integer, decimal, money, or smallmone data type is required.

Do not use float or real columns in where clause search conditions (especially the = and <> operators. It is best to restrict the use of float and real columns for> or <comparison.

The IEEE 754 specification provides four rounding modes: Rounding to the nearest value, rounding up, downloading, and rounding to zero. Microsoft SQL Server uses top rounding. All values must be accurate to the specified precision, but small floating point values may change. Because the binary representation of floating point numbers can use any of the many valid rounding rules, it is impossible for us to reliably quantify a floating point value.

 

Address: http://www.cnblogs.com/qiantuwuliang/archive/2009/06/06/1497469.html