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

Question

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

Answer 1

There's not much left to say as the article you mention yourself already covers it nicely.

Are standards important to you? If your answer is 'yes', then you should be using SET.

As for my own standards, I don't really use one over the other, it depends on the alignment of the planets. After reading the article, I must confess I have a slight preference for SET.

Answer 2

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)