Is Decimal best Datatype for data with 2 decimals?

Question

Is Decimal best Datatype for data with 2 decimals?

repent_kog_is_near

SSCertifiable

Points: 7278
More actions
October 25, 2009 at 7:06 pm

#98258

For data like percentage,Ratios,Rates, liters, kilometers etc with 2 decimals, is decimal always the best bet for a data type, in SQL Server 2005?
Example of these data:
50.6%
6.50% tax
1025.25 liters
540.54 Kms
1.25 Ratio.
Thanks
Dan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736118 More actions · Answer 1

I don't think there's a best. Not sure for that level of precision that decimal v numeric matters. I wouldn't pick float or real, as it's not necessary.

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 2

repent_kog_is_near (10/25/2009)
For data like percentage,Ratios,Rates, liters, kilometers etc with 2 decimals, is decimal always the best bet for a data type, in SQL Server 2005?
Example of these data:
50.6%
6.50% tax
1025.25 liters
540.54 Kms
1.25 Ratio.
Thanks
Dan

For something like mortgage rates and related calculations, then I'd say to go with the max and save the 1 or 2 decimal places for display purposes only. Otherwise, I'm with Steve... "It Depends". If you have a bazillion rows that each have a "tolerance" buildup, then you need more decimal places. If you're calculating Tax on a grocery bill, the 4 places rounded to 2 for display purposes should probably be just fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 3

Steve, Jeff

With decimal and Numeric being the same,

http://msdn.microsoft.com/en-us/library/ms187746.aspx

there is no other choice, for these kind of data, right? with the only decision to make will be how much scale does the data need (2 or 4 or how many ever decimals), and how much rounding can be tolerated.

With the grocery example, which is one of the calculations I will be handling, it will be decimal x decimal (amount * tax) = decimal. On this point, do you see any problems using decimal for money related columns? (rounding to 2 decimals is ok, by business rules).

Thanks

Dan

Steve Jones - SSC Editor SSC Guru Points: 736118 More actions · Answer 4

I don't see a problem for decimal for money. In fact, I've never used the money data type. I don't trust that business rules are really that set, especially with finances. I plan for them to change, and for some flexibility. I'd rather run the CAST or ROUND in my stored procs to account for current business rules and allow for 4 or 5 places in storage.

developer6 Right there with Babe Points: 717 More actions · Answer 5

What's a bazillion? Is that more than a gazillion? 🙂

Steve Jones - SSC Editor SSC Guru Points: 736118 More actions · Answer 6

Steve Jones - SSC Editor

SSC Guru

Points: 736118

October 27, 2009 at 8:12 am

#1071229

If you have to ask....

developer6 Right there with Babe Points: 717 More actions · Answer 7

Okey doke, I think I'm with it now. They're synonymous right -- both mean an unspecified large number. 😎