Question: What is the difference of performance between SELECT and SET?
SELECT : Designed to return data.
SET : Designed to assign values to local variables.
While testing the performance of the following two scripts in query analyzer, interesting results are discovered.
SET @foo1 = 1; SET @foo2 = 2; SET @foo3 = 3; SELECT @foo1 = 1, @foo2 = 2, @foo3 = 3;
While comparing their performance in loop SELECT statement gives better performance then SET. In other words, SET is slower than SELECT. The reason is that each SET statement runs individually and updates on values per execution, whereas the entire SELECT statement runs once and update all three values in one execution.
SET is the ANSI standard for variable assignment, SELECT is not. SET can only assign one variable at a time, SELECT can make multiple assignments at once – that gives SELECT slight speed advantage over SET. If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all keeping the variable unchanged.
Reference : Pinal Dave (https://blog.sqlauthority.com)
ANSI versus Microsoft dialect is messier than just this. In ANSI, the construction is always “SELECT .. FROM..” ; there is never a SELECT without a FROM clause. The SET operator can use a row constructor to do what T-SQL proprietary syntax does.
SET (a,b,c) = (x,y,z);
But it can also do more complex assignments.
When using the row constructor, does it still execute each individually (making it still slower than SELECT)?
I would say that it is better to use SET if you want to assign a value to a variable. Some more examples at