Hi All,

This may look like a duplicate to here, but it's not. I am trying to get a best practice, not a technical answer (which i already (think) i know).

New to SQL Server and trying to form good habits.
I found a great explanation of the functional differences between SET @var = and SELECT @var =
here: http://vyaskn.tripod.com/differences_between_set_and_select.htm
To summarize what each has that the other hasn't (see source for examples):

SET:

1. ANSI and portable, recommended by Microsoft.
2. SET @var = (SELECT column_name FROM table_name) fails when the select returns more then one value, eliminating the possibility of unpredictable results.
3. SET @var = (SELECT column_name FROM table_name) will set @var to NULL if that's what SELECT column_name FROM table_name returned, thus never leaving @var at it's prior value.

SELECT:

1. Multiple variables can be set in one statement
2. Can return multiple system variables set by the prior DML statement
3. SELECT @var = column_name FROM table_name would set @var to (according to my testing) the last value returned by the select. This could be a feature or a bug. Behavior can be changed with SELECT @j = (SELECT column_name FROM table_name) syntax.
4. Speed. Setting multiple variables with a single SELECT statement as opposed to multiple SET/SELECT statements is much quicker. He has a sample test to prove his point. If you could design a test to prove the otherwise, bring it on!

So, what do i do?

• (Almost) always use SET @var =, using SELECT @var = is messy coding and not standard.

  OR

• Use SELECT @var = freely, it could accomplish more for me, unless the code is likely to be ported to another environment.

Thanks