DECLARE Variables in SQL Server: One Big Statement vs Many Small Statements

I heard that one big DECLARE statement is better than a plenty of small statements. I tried to check which approach is really better. To find the answer I’ve created two stored procedures OneBigDeclare and ManySmallDeclare. Also I’ve written a simple test that executes 100 cycles. Each cycle executes each stored procedure 10 thousand times.

All source codes can be downloaded fromĀ declare_execution_test

My results
Average execution time of a cycle:
OneBigDeclare – 165.62ms
ManySmallDeclare – 171.12ms

It seems that one big DECLARE statement is better for just 3.3%. But when you look at the graph of cycle’s execution time you see that it’s not always true. On X axis is the sequence number of a cycle, on the Y axis is the execution time in milliseconds.

Declare test - execution time graph

So I can’t prove that one big DECLARE statement is really better.

SQL Server Data Types: MONEY vs DECIMAL

I’ve seen some notes that we should avoid using MONEY data types in SQL Server. But why?

At first glance it seems that MONEY data types is similar to NUMERIC/DECIMAL with precision 19 and scale 4. It can be check very easily – just assign maximum value of MONEY data type to decimal variables with different precision.

money is like decimal(19.4)
As you see we could not able to assign such a big value to DECIMAL(18,4).
Great! Let’s go further. The next question that arise – what’s the difference between MONEY and DECIMAL(19,4)? It’s not so obvious to find it. It lies in rounding of arithmetic operations.
money and decimal rounding
The results of division are the same, but the results of division and multiplication are not. I suppose you understand where the rounding occurred.
I think it’s a good advice not to use MONEY data type in SQL Server databases.