Question: What is the difference of performance between SELECT and SET?
Answer:
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.
How I Choose Between SELECT and SET in Real Code
The speed difference matters only in tight loops that run many thousands of times. In normal code, behavior matters much more than speed, so I decide based on what the query returns.
When I assign several variables from one row of a table, I use SELECT, because it reads the row once. When a value must come from exactly one row, I use SET with a subquery. If the data ever changes and the subquery returns two rows, SET fails loudly with an error, while SELECT quietly picks one of the values and moves on.
The no rows case needs extra care inside a loop. Since SELECT leaves the variable unchanged, it can keep the value from the previous pass, and that bug is very hard to spot. Reset the variable to NULL before each assignment, or check @@ROWCOUNT right after the SELECT to see how many rows were found.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
3 Comments. Leave new
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