It doesn't have a major performance difference between the Set and Select
statement. The only thing is the usability nature of the commands.
SET @variable = (SELECT datacolumn FROM tablename)
The above statement should return a
single value which will be assigned to the variable. Here, if the results of
the subquery expression is empty then @variable has a value of NULL but if the results
of the subquery are more than one row then you get a error.
And thus it makes sense while,
SELECT @variable
= columnvalue FROM dbo.tablename
returns a value from a number of rows and
we are not sure what's the criteria in selecting that row.
One
major difference in assigning using SET and SELECT is:
SET @variable1 =
'somevalue', @variable2 = 'someothervalue'
This is NOT possible but the same is possible
with SELECT as in:
SELECT @variable1 =
'somevalue', @variable2 = 'someothervalue'