Category: SQL Server

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.

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

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.