Difference between numeric, float and decimal in SQL Server

by

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

Exact Numeric Data Types
Approximate Numeric Data Types

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

More Related Contents:

  1. What is SYSNAME data type in SQL Server?
  2. SQL Server equivalent to MySQL enum data type?
  3. What is the most appropriate data type for storing an IP address in SQL server? [duplicate]
  4. Get Parent with multiple children in desc order
  5. How to calculate age (in years) based on Date of Birth and getDate()
  6. DateTime2 vs DateTime in SQL Server
  7. How to generate a range of numbers between two numbers?
  8. T-SQL dynamic pivot
  9. Generate Dates between date ranges
  10. Select group of rows that match all items in a list
  11. Is there any difference between IS NULL and =NULL
  12. How to print VARCHAR(MAX) using Print Statement?
  13. SQL statement to select all rows from previous day
  14. Default row order in SELECT query – SQL Server 2008 vs SQL 2012
  15. What does =* mean?
  16. How can I combine multiple rows into a comma-delimited list in SQL Server 2005?
  17. SQL Select Upcoming Birthdays
  18. SQL : in clause in stored procedure:how to pass values
  19. How can I include null values in a MIN or MAX?
  20. PostgreSQL ORDER BY issue – natural sort
  21. Favourite performance tuning tricks [closed]
  22. Best way to do nested case statement logic in SQL Server
  23. Query a database based on result of query from another database
  24. How to delete the top 1000 rows from a table using Sql Server 2008?
  25. CHECK CONSTRAINT on multiple columns
  26. SQL count consecutive days
  27. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  28. Convert bigint to datetime
  29. SQL SERVER DATETIME FORMAT
  30. Insert all values of a table into another table in SQL

Leave a Comment