Difference between numeric, float and decimal in SQL Server

 

I searched in Google and also visited decimal and numeric and SQL Server Helper to glean the difference between numeric, float and decimal datatypes and also to find out which one should be used in which situation.

For any kind of financial transaction (eg for salary field), which one is prefered and why?

use the float or real data types only if the precision provided by decimal (up to 38 digits) is insufficient

  • Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value.(Technet)

  • Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators (Technet)

    • so generally because the precision provided by decimal is [10E38 ~ 38 digits] if your number can fit in it, and smaller storage space (and maybe speed) of Float is not important and dealing with abnormal behaviors and issues of approximate numeric types are not acceptable, use Decimal generally .

    more useful information

  • numeric = decimal (5 to 17 bytes) ( Exact Numeric Data Type)
  • will map to Decimal in .NET
  • both have (18, 0) as default (precision,scale) parameters in SQL server
  • scale = maximum number of decimal digits that can be stored to the right of the decimal point.
  • kindly note that money(8 byte) and smallmoney(4 byte) are also exact and map to Decimal In .NET and have 4 decimal points(MSDN)
  • decimal and numeric (Transact-SQL) - MSDN
  • real (4 byte) ( Approximate Numeric Data Type)
  • will map to Single in .NET
  • The ISO synonym for real is float(24)
  • float and real (Transact-SQL) - MSDN
  • float (8 byte) ( Approximate Numeric Data Type)
  • will map to Double in .NET
  • All exact numeric types always produce the same result, regardless of which kind of processor architecture is being used or the magnitude of the numbers
  • The parameter supplied to the float data type defines the number of bits that are used to store the mantissa of the floating point number .
  • Approximate Numeric Data Type usually uses less storage and have better speed (up to 20x) and you should also consider when they got converted in .NET
  • What is the difference between Decimal, Float and Double in C#
  • Decimal vs Double Speed
  • SQL Server - .NET Data Type Mappings (From MSDN)

    • 准确的数字数据类型近似的数字数据类型

    main source : MCTS Self-Paced Training Kit (Exam 70-433): Microsoft® SQL Server® 2008 Database Development - Chapter 3 - Tables , Data Types , and Declarative Data Integrity Lesson 1 - Choosing Data Types (Guidelines) - Page 93

    Guidelines from MSDN: Using decimal, float, and real Data

    The default maximum precision of numeric and decimal data types is 38. In Transact-SQL, numeric is functionally equivalent to the decimal data type. Use the decimal data type to store numbers with decimals when the data values must be stored exactly as specified.

    The behavior of float and real follows the IEEE 754 specification on approximate numeric data types. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types. Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

    Not a complete answer, but a useful link:

    "I frequently do calculations against decimal values. In some cases casting decimal values to float ASAP, prior to any calculations, yields better accuracy. "

    http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/12/20/for-better-precision-cast-decimals-before-calculations.aspx

    链接地址: http://www.djcxy.com/p/21354.html

    上一篇: typedef结构与结构定义

    下一篇: SQL Server中的数字，浮点数和小数点之间的区别