Speed of multiple variable assignment in T-SQL

Question

Imagine I have a chunk of initialisation code at the top of a stored procedure with a number of variable assignments:

SET @proc = 'sp_madeupname'
SET @magic_number = 42
SET @tomorrows_date = DATEADD(dd, 1, GETDATE())
...

Clearly doing all of the above as one SELECT would be faster:

SELECT
     @proc = 'sp_madeupname'
    ,@magic_number = 42
    ,@tomorrows_date = DATEADD(dd, 1, GETDATE())
...

But how much faster? Say if this stored procedure was executed as part of a loop, several thousand times, is it going to make any significant difference to the performance?

Answer 1

I've thought about it but never tested it.

In my experience, the optimizer is pretty good, so I would think it makes no difference, but I'd run some tests if you thought it was really useful.

I think doing multiple assignments can be useful from a maintenance point of view, if you want some things which should always be done together to not be broken up with a cut and paste or refactoring.

For the same reason, code which is relatively modular can benefit from separate initialization, since this is more easily cut and pasted to refactor during maintenance.

Answer 2

In this case, SELECT wins, performance-wise, when performing multiple assignments.

Here is some more information about it:

SELECT vs. SET: Optimizing Loops