May 6, 2009 at 1:05 am
Hi Every1,
I had a debate with my collegue on using the numeric datatypes... flaot, decimal, real, numeric.
The debate started from one scenario where we have to store the the numeric data the client told us database should maintain the numeric data upto specified digits (specified on screen as well as in database) only. I have putted some examples below, your suggestions are most appreciated....
i have given below example...
declare @tbl table( nFloat float, nFloat8 float(8), nReal real, ndouble decimal(18,2), nNumeric Numeric(18, 2) )
insert @tbl
values ( 999999999.999999, 999999999.999999, 999999999.999999, 999999999.999999, 999999999.999999 )
select * from @tbl
What I explain is, If we use float data type it will store the specified length with correct values. we could handle the lenght from Application. (correct me if i am wrong!)
Experts! I need your comments on this debate. Thanks in advance.
Abhijit - http://abhijitmore.wordpress.com
May 6, 2009 at 7:40 am
From BOL:
Using float and real Data
The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.
Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. 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.
The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server 2005 uses round up. All are accurate to the guaranteed precision but can result in slightly different floating-point values. Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 6, 2009 at 8:21 am
Just found a duplicate post for this one with more replies so please post replies here.
Also please don't duplicate post as it fragments replies.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 6, 2009 at 8:40 am
closing
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply