Difference between numeric, float and decimal in SQL Server
Posted By: Anonymous
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?
Solution
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 (TransactSQL) – 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 (TransactSQL) – 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
main source : MCTS SelfPaced Training Kit (Exam 70433): Microsoft® SQL Server® 2008 Database Development – Chapter 3 – Tables , Data Types , and Declarative Data Integrity Lesson 1 – Choosing Data Types (Guidelines) – Page 93
Answered By: Anonymous
Disclaimer: This content is shared under creative common license ccbysa 3.0. It is generated from StackExchange Website Network.