May 6, 2009 at 6:49 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....
--------------------------------------------------------------------------------
Examples
[/hr]
I have a client request to store numeric data upto 3 digits / 6 digits / 9 digits. format should be decimal number with 3 significant digits / decimal number with 6 significant digits / decimal number with 9 significant digits and total field length should be 7/ 11 respectively. e.g...999.000/98.0934/98.0934122
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:05 am
Front end application code should handle field length at the GUI level.
In regards to the back end datatypes you may want to take a look at this: http://www.databasejournal.com/features/mssql/article.phpr/2212141/Choosing-SQL-Server-2000-Data-Types.htm
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 6, 2009 at 7:41 am
It really depends on the business requirements. But if you're dealing with hard numbers, I'd use decimal instead of float. Float is an approximate number. This is from the BOL:
Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.
However, somewhere online here you can find an example where Jeff Moden shows how to break the decimal data type. I forget where it's located or what the context was.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2009 at 8:20 am
Okay my reply is on this thread.
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 9:07 am
Grant Fritchey (5/6/2009)
It really depends on the business requirements. But if you're dealing with hard numbers, I'd use decimal instead of float. Float is an approximate number. This is from the BOL:Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.
However, somewhere online here you can find an example where Jeff Moden shows how to break the decimal data type. I forget where it's located or what the context was.
You might be thinking of this thread http://www.sqlservercentral.com/Forums/Topic544518-9-1.aspx.
I remember an earlier thread on the subject where I think Matt Miller brought up the subject and had a bunch of proofs that floating point was better for monetary calculation than the money data type, but I can't find that thread right now.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2009 at 3:00 am
hey sorry Jack, for duplicate post its my bad.
thanks for your suggestions...:-)
Abhijit - http://abhijitmore.wordpress.com
May 8, 2009 at 1:40 am
An application could handle field length, number of decimal places, etc -- in one layer or another -- however, I wouldn't dismiss it as a database issue. Obviously data integrity constraints could be handled by an application but many of these are typically enforced at the database level.
What do others think?
May 8, 2009 at 2:55 am
IMHO a lot depends on the purpose of the DB - eg in a generic, reusable invoicing system you would want to store everything with as much detail as possible in the back end, formatting it down in either the middle or gui layers - this gives you flexibility should one customer all of a sudden decide their invoice quantities go to 12 decimal places ;o)
On the other hand, if you have a specific customer requirement and the DB is only for their use it would make sense to put these restrictions in the database - for example, for some scientific/financial/engineering applications there are set requirements for decimal places and/or significant figures, and not reflecting them in your database could lead to issues later on with rounding etc.
From a personal point of view, I try to avoid float for the reasons already outlined.
Regards
Samuele Armondi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply