SELECT SQL Variable - should i avoid using this syntax and always use SET?

Question

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

Answer 1

Here is my opinion - use SET for simple operations such as SET @var = 'hardcoded_value' and use SELECT for doing tricker assignments such as from a table. I almost always end up writing select into variable statements in the following way to make my intentions clear to both the compiler and any other developers: SELECT TOP 1 @var = col_name FROM some_table

If I was worried about portability I wouldn't be writing T-SQL and instead would stick with an ORM layer for data access instead.

Edit, bonus tip: In SQL 08 I like using this syntax which is fairly terse for T-SQL:

DECLARE @var int = (SELECT col_name FROM some_table)