SQL Server 中数字、浮点数和小数之间的区别

2022-01-31 00:00:00 sql types sql-server

numeric、float和decimal这三种数据类型有什么区别，分别在什么情况下使用?

对于任何一种金融交易(例如薪金领域)，首选哪一种?为什么?

解决方案

使用 float 或 real 数据类型仅当 decimal 提供的精度(最多 38 位) 不足

  • 近似数值数据类型(见表 3.3)不存储为许多数字指定的精确值；它们存储非常接近的近似值.(.

  • 近似数值数据类型通常使用更少的存储空间并且速度更快(高达 20 倍)，您还应该考虑何时将它们转换为 .NET
  • C#中的Decimal、Float和Double有什么区别
  • 十进制与双倍速度
  • SQL Server - .NET 数据类型映射(来自 MSDN)

主要来源:MCTS Self-Paced Training Kit(考试 70-433):Microsoft® SQL Server® 2008 数据库开发 - 第 3 章 - 表、数据类型和声明性数据完整性第 1 课 - 选择数据类型(指南) - 第 93 页

What are the differences between numeric, float and decimal datatypes and which should be used in which situations?

For any kind of financial transaction (e.g. for salary field), which one is preferred 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(see table 3.3) 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. It is best to limit float and real columns to > or < comparisons. (Technet)

so generally choosing Decimal as your data type is the best bet if

  • your number can fit in it. Decimal precision is 10E38[~ 38 digits]
  • smaller storage space (and maybe calculation speed) of Float is not important for you
  • exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. (Technet)

  1. Exact Numeric Data Types decimal and numeric - MSDN

  • numeric = decimal (5 to 17 bytes)
    • 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.
    • money(8 byte) and smallmoney(4 byte) are also Exact Data Type and will map to Decimal in .NET and have 4 decimal points (MSDN)

  1. Approximate Numeric Data Types float and real - MSDN

  • real (4 byte)
    • will map to Single in .NET
    • The ISO synonym for real is float(24)
  • float (8 byte)
    • 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

相关文章