November 19, 2014 at 4:48 pm
iammichaelclark 21625 (11/18/2014)
Hi,I am having two columns with the money datatype values.
Column1 Column2
$134,456 $15,897
$123 $78,556
But when try to sum these values I get an error message like this "Operand data type nvarchar is invalid for sum operator".
Really appreciate any help on this.
Is there a cast or convert occurring on these columns somewhere in your code, Or are you sourcing from a view that perhaps is casting the money as Nvarchar within?
----------------------------------------------------
November 19, 2014 at 5:47 pm
ksatpute123 (11/18/2014)
Disadvantages of money datatype:It is proprietary, so porting it is a pain. It is one of the many "Sybase Code Museum" features from decades ago. Remember the early versions of UNIX?
Writing code in dialect when you don't need to make you sound like a hillbilly to people that speak the language. You are better off with DECIMAL(s,p) so you can use a properly sized column.
It does display and formatting in the back end, with commas and dollar signs. That defeats the purpose of a tiered architecture.
The MONEY data type has rounding errors.
Ref:msdn
Details taken from: http://stackoverflow.com/questions/22343185/when-to-use-money-data-or-decimal-data-type-in-sql-server-to-store-costing-value
See also
I hope this clarifies your doubts.
There's nothing wrong with proprietary code because, except for the possibility of basic C.R.U.D., portability is a myth.
I'd also never cite a "performance" article that didn't have the test code attached because a lot of people don't know how to test. For example, one of the articles claimed in a chart with no code to back it up that it took somewhere between roughly 47 and 53 seconds to insert numeric values into a single column table. Even my 12 year old, 32 bit XP box will do a million rows in about 4 seconds.
As for avoiding the MONEY datatype because of reasons of performance, I'll say what is now and has always been in this business, "IT DEPENDS". Unlike others, here's some code that you can play with. Do notice that it takes nowhere near 47-53 seconds to insert a plaltry 390K rows and, except for While Loops or Recursive CTEs, I'm actually using one of the slower methods to create the rows.
--===== Conditionally drop the test tables to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestDecimal92','U') IS NOT NULL
DROP TABLE #TestDecimal92
;
IF OBJECT_ID('tempdb..#TestMoney','U') IS NOT NULL
DROP TABLE #TestMoney
;
--===== Create the test tables named after the datatype they hold.
CREATE TABLE #TestDecimal92
(SomeValue DECIMAL(9,2)) --9 digits is the largest that still only requires 5 bytes of storage.
;
CREATE TABLE #TestMoney
(SomeValue MONEY)
;
--===== "Prime the pump" so that all code snippets have the same cache advantage
DECLARE @Bitbucket INT
SELECT TOP 1000000 @Bitbucket = 1
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
--===== Do 390,000 random number inserts with random values from 0 up to and not including 10,000,000
SET STATISTICS TIME OFF;
PRINT '========== INSERT MONEY ========================================================';
SET STATISTICS TIME ON;
GO
INSERT INTO #TestMoney
(SomeValue)
SELECT TOP 390000
RAND(CHECKSUM(NEWID()))*10000000
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO 3
SET STATISTICS TIME OFF;
PRINT '========== INSERT DECIMAL(9,2) =================================================';
SET STATISTICS TIME ON;
GO
INSERT INTO #TestDecimal92
(SomeValue)
SELECT TOP 390000
RAND(CHECKSUM(NEWID()))*10000000
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO 3
SET STATISTICS TIME OFF;
Here are the results from that code and, yes, I tried changing the places of the two snippets and it didn't matter... the MONEY datatype beat the DECIMAL datatype pretty handily in each case.
========== INSERT MONEY ========================================================
Beginning execution loop
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2465 ms, elapsed time = 2489 ms.
(390000 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2481 ms, elapsed time = 2482 ms.
(390000 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2449 ms, elapsed time = 2478 ms.
(390000 row(s) affected)
Batch execution completed 3 times.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
========== INSERT DECIMAL(9,2) =================================================
Beginning execution loop
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2714 ms, elapsed time = 2753 ms.
(390000 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2730 ms, elapsed time = 2758 ms.
(390000 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2762 ms, elapsed time = 2770 ms.
(390000 row(s) affected)
Batch execution completed 3 times.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Let's try INTEGERs instead of Floating point random numbers...
--===== Conditionally drop the test tables to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestDecimal92','U') IS NOT NULL
DROP TABLE #TestDecimal92
;
IF OBJECT_ID('tempdb..#TestMoney','U') IS NOT NULL
DROP TABLE #TestMoney
;
--===== Create the test tables named after the datatype they hold.
CREATE TABLE #TestDecimal92
(SomeValue DECIMAL(9,2)) --9 digits is the largest that still only requires 5 bytes of storage.
;
CREATE TABLE #TestMoney
(SomeValue MONEY)
;
--===== "Prime the pump" so that all code snippets have the same cache advantage
DECLARE @Bitbucket INT
SELECT TOP 1000000 @Bitbucket = 1
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
--===== Do 390,000 random number inserts with random values from 0 up to and not including 10,000,000
SET STATISTICS TIME OFF;
PRINT '========== INSERT MONEY ========================================================';
SET STATISTICS TIME ON;
GO
INSERT INTO #TestMoney
(SomeValue)
SELECT TOP 390000
ABS(CHECKSUM(NEWID()))%10000000
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO 3
SET STATISTICS TIME OFF;
PRINT '========== INSERT DECIMAL(9,2) =================================================';
SET STATISTICS TIME ON;
GO
INSERT INTO #TestDecimal92
(SomeValue)
SELECT TOP 390000
ABS(CHECKSUM(NEWID()))%10000000
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO 3
SET STATISTICS TIME OFF;
Here are the results from that... Again, MONEY wins!
========== INSERT MONEY ========================================================
Beginning execution loop
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 13 ms.
SQL Server Execution Times:
CPU time = 2356 ms, elapsed time = 2359 ms.
(390000 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2324 ms, elapsed time = 2361 ms.
(390000 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2356 ms, elapsed time = 2366 ms.
(390000 row(s) affected)
Batch execution completed 3 times.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
========== INSERT DECIMAL(9,2) =================================================
Beginning execution loop
SQL Server parse and compile time:
CPU time = 12 ms, elapsed time = 12 ms.
SQL Server Execution Times:
CPU time = 2511 ms, elapsed time = 2536 ms.
(390000 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2527 ms, elapsed time = 2542 ms.
(390000 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2496 ms, elapsed time = 2542 ms.
(390000 row(s) affected)
Batch execution completed 3 times.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
There may be places where MONEY loses for performance but, as you can clearly see, MONEY doesn't always lose for performance. Saying that MONEY is a performance problem during INSERTs is as big a myth as true portability.
Shifting gears a bit, even though it still lost to MONEY, notice that DECIMAL did better on the INTEGER run that it did the FLOATING point run. That's also a hint that even if someone posts code to supposedly "prove" something, you better make damned sure they haven't accidently skewed the results simply because they didn't pay attention to datatype and THAT happens a whole lot more that anyone could imagine possible.
Stop trusting... VERIFY!
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2014 at 8:31 pm
iammichaelclark 21625 (11/18/2014)
Hi,I am having two columns with the money datatype values.
Column1 Column2
$134,456 $15,897
$123 $78,556
But when try to sum these values I get an error message like this "Operand data type nvarchar is invalid for sum operator".
Really appreciate any help on this.
Getting back to the original question...
The fact that you have $ signs and commas also explains the error. The columns are NOT the MONEY datatype. The MONEY datatype will NOT store currency symbols of commas. Only character based datatypes can do that and you can't "sum" character based datatypes (unless an implicit conversion takes place). If you look at the error you posted above, it's clearly stating that you're dealing with the NVARCHAR datatype and NOT the MONEY datatype.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2014 at 9:43 pm
ksatpute123 (11/18/2014)
I'm not saying money is the best thing ever and it sure has it's issues, but just to go around declaring it should never be used is one step too far for me 🙂 It's OK to use it if you use it for what it is supposed to be used and if you know the limitations of the data type.
(Using money data type can have a good performance impact on the processing of SSAS cubes for example)
I completely agree but most of the times we do have to do a lot data massaging/calculations and money data types are not very reliable for arithmetic operations due to their rounding off issues.
In case if we are talking about accuracy VS little performance hit. I say accuracy should be the winner.
On the subject of "accuracy VS little performance hit", I absolutely agree that accuracy should be the winner. I also agree that the MONEY datatype does have a rounding "problem" and that it is, in fact, quite different than DECIMAL(19,4) or NUMERIC(19,4). Someone else (Bhavik Solanki, to be specific) on the "Celko" thread that you posted gave a pretty good demonstration of the rounding "problem" that can occur if ALL the datatypes in an equation are of the MONEY datatype AND something other than simple addition or subtraction (including SUMs) is used.
Because of the rounding "problem" that MONEY does have, I won't use MONEY for anything except to make it easier to convert provided (usually a file import for me) currency amounts (has currency symbol and or commas) into DECIMAL amounts for final storage in the database. It means that I don't have to have an iTVF that strips out currency marks and commas. I can store the currency representation into the MONEY datatype and then cast it to the DECIMAL or NUMERIC datatype with the appropriate precision and scale.
There comes the next rub. People often make the mistake of doing monthly calculations for things like interest payments and then storing the balance as a DECIMAL(x,2) or NUMERIC(x,2) and then they wonder why "Grandma" calls the cops because you're a penny or two off next month. If you're doing such monthly calculations and saving the balance with a scale of 2, you're making a huge and terrible mistake and "Granny" WILL get you. The mistake is the rounding that occurs when you convert whatever the balance is to have a scale of only 2 or even of only 4. NEVER store running balances of things like mortgage interest calculations as a displayable value with just 2 or 4 decimal points. You should always store it with a scale of at least 15, just like any decent 4 function calculator does (they typically calculate using a scale of 15 and then display a rounded value with a scale of up to 13 max).
Ok, why do I keep putting the word "problem", as in rounding "problem" in quotes? Because it's not a problem. It was designed that way and isn't any more of a problem than ISNUMERIC returning 1 for currency symbols or a string of nothing but commas. The real "problem" is that the MS documentation doesn't come right out and say "if you do this, you're dead because that's not what it was designed to do or provide". Used correctly, the MONEY datatype is a great tool. Used incorrectly and "Granny" will beat you to death with her change purse. 😛 Like the old Dunkin' Donuts commercial used to say, "See you in court, sonny!". :hehe:
Revisiting the subject of portability once again, I'm not going to sacrifice performance for portability like I will for accuracy. Like I said, the MONEY datatype is a great tool for stripping out currency symbols and commas in T-SQL. Not using proprietary tools in T-SQL is like saying you can't use the advance functionality of your scientific calculator just because someone else only has a 4 function calculator. To coin a phrase, "Got tools? USE THEM!" 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2014 at 1:54 am
WOW! This discussion is getting a lot of interesting replies and in depth analysis. I just hope that the OP is getting as much knowledge out of this as I am.
Thank you Jeff for the analysis and clarification backed up by results. I will make sure I do the same from now on. My replies were based on personal experience handling the Money data types but I guess a little more digging up from now on won't hurt. 😀
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply