In my previous post I compared one big DECLARE statement vs many small statements. The result was there’s no significant difference between these two approaches. This time I wonder is assignment sensitive to how it is implemented? I mean if you will use one big SELECT command or many small SELECT or SET commands.
At first I would clarify that there’s no difference to SQL Server how you write a single assignment – with SELECT or SET command. Just look at the execution plan – each command imply the ASSIGN operation.
For experiment I use stored procedure from previous test and write a new one that implement one assignment in SELECT command. If we look at estimated execution plan we’ll see that first stored procedure OneBigDeclare has 30 ASSIGN operation, on the other side the stored procedure OneBigDeclareWithBigSelect has only one ASSIGN operation. Probably the speed of the second SP will be higher.
All source code can be downloaded from assignment_execution_test
My results
Average execution time of a cycle:
OneBigDeclare – 77.98ms
ManySmallDeclare – 140.22ms