SET or SELECT -- is there any performance difference?

It doesn't have 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' 

Cheers,

Venkatesan Prabu .J

Head, KaaShiv InfoTech

For free Ebook : http://kaashivinfotech.com/Ebooks.aspx