Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a dollar amount value, that for historical reasons has always just been stored in an nvarchar field. Now that we are storing many more rows than we have in the past, and are using this field to total up amounts using an application that will automatically refresh frequently, I'm concerned about performance.

But I'm also concerned about the cost of making a change like this late in the development cycle.

Soon this database will be used by a customer that will generate millions of rows. All of these rows will not participate in this calculation. The rows are divided into groups of around 10,000, each parent of the group will be processed every 10 minutes or so, which is when these totals will be calculated.

Is it worth it now to change the column type to money, including the stored procedures, UDT's, data layer, etc?

It seems like it could have a performance impact, but unfortunately, I can't generate enough volume with the resources I have to do performance tests thta would be realistic. So I'm hoping someone has experience with a string to number conversion and can give me an idea if doing this conversion on 10,000 rows at a time will be a problem.

share|improve this question
5  
You probably don't want to use MONEY--go DECIMAL. See stackoverflow.com/questions/582797/… –  SQLFox May 16 at 17:52
1  
Mostly I was thinking of Aaron's arguments against it, but sqlblog.com/blogs/aaron_bertrand/archive/2009/10/12/… doesn't have a lot of detail, and the SO question he links to suffers the same problem of the accepted answer not having the interesting info –  SQLFox May 17 at 17:05
    
Thank you for that link to Aaron Bertrand's blog. I found it very enlightening (and will hopefully correct many of those bad habits before launch). –  Dean May 18 at 0:30

2 Answers 2

up vote 13 down vote accepted

Performance cost of cast is minor. Database cost is usually in IO and a cast seldom even shows on the radar.

But that is not the problem. The real problem is going to be data purity. If you allow arbitrary nvarchar then rest assured, non-numeric values will show up in the database. Despite every precaution you take in the app, experience tells us that down the road somewhere somehow will insert 'abagft' as the value. And then the casts will start throwing runtime exceptions and you (or whoever will be running the show then) will curse and curse and curse. And you'll try to 'fix' it with a boolean shortcircuit WHERE, just like everybody else, and it won't work. This road is paved with bodies, trust me.

There are also problems related to data type precedence, type inference in dev tools (fields ends up as C# string) and, most importantly, index seek sargability problems.

Use a numeric type for numeric values. DECIMAL is good for moneys. And the time for change is always before you go live with millions of values. Right now is the cost of code change and risk of regression. In 6 months will be the same cost of code change and risk of regression plus downtime and size-of-data update operation.

share|improve this answer
1  
+1 Ahhh, the stench of blood-curdling experience. –  Max Vernon May 16 at 18:21
3  
first I read 'blood-curling' and was thinking 'that's a new nice metaphor' :) lol –  Remus Rusanu May 16 at 18:24
1  
A cast by itself may be cheap, but a cast, if used on a column in a where clause, can have a nasty side effect of forcing a table scan when an index seek would have sufficed without the cast. And a table scan may use a lot of I/O, which as you mentioned, is the usual cost in a database. –  Brandon May 17 at 2:47
2  
Changed this to the accepted answer, as it does contain more information and guidance. Even though Max Vernon answered first, and was helpful to me as well. –  Dean May 18 at 0:23

Define "a problem".

If you mean will it take vastly longer and be a management nightmare, then, yes.

Convert the field to DECIMAL(18,4) or maybe (10,2) if you don't care about sub-penny precision.

share|improve this answer
2  
Thank you. Just wanted to feel justified in all the changes I have to make for it. (Which still isn't more than a couple hours work.) In house DBA's were not nearly so forthcoming. –  Dean May 16 at 18:11
    
So DECIMAL(18,4) rather than the money type, huh? And i don't need more than 10,2 at the moment but it's always possible someone will say "We have to support Korean WON or something and I'll wish I had 4 decimal places. –  Dean May 16 at 18:14
    
4 decimal places is useful if you are concerned about proving that you don't have a rounding issue. Conversely, it makes handling rounding issues more difficult. In short, always ensure you have enough magnitude and mantissa for your needs. –  Max Vernon May 16 at 18:15
1  
In-house DBAs will almost always say "it depends". That is not because they are trying to be difficult, but because it really does depend; and they are not usually willing to put their neck out without understanding the full implications. Here, we have the luxury of not being held responsible for poor decision-making. –  Max Vernon May 16 at 18:17
1  
These are all user entered values, not calculated. No math is performed on them other than showing totals for monitoring purposes. Rounding errors shouldn't be a problem. But that doesn't mean I won't need more decimal places. So I'll stick with your suggestion of 18,4 –  Dean May 16 at 18:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.