Since SET was introduced in SQL Server 7.0 there is an unending debate going on between database programmers whether to use SET or SELECT. There are several difference between SET & SELECT. Here we will see a comparison of SELECT vs SET and the major difference between them especially in the areas of user friendliness, performance, error handling and troubleshooting.
Performance
Let’s start with performance by comparing them using Execution Plan. Let’s consider that you have to assign a single variable and see the performance difference between SET and SELECT. If you run the execution plan, you can see both the statements consuming same percentage of query cost. So, if you need to assign only one variable, you can go for SET.
DECLARE @var1 AS int; SET @var1 = 1 SELECT @var1 = 10 GO
However, If you run the execution plan for multiple variable assignments, then, you can see the SET operations take more time as it needs to be executed multiple times based on the number of variables. So, if your stored procedure, function or block has many variable to assign, then consider using SELECT.
DECLARE @var1 AS int, @var2 AS int, @var3 AS int; SET @var1 = 1 SET @var2 = 2 SET @var3 = 3 SELECT @var1 = 10, @var2 = 20, @var3 = 30
Troubleshooting
When assigning values from a select query, if the query returns more than one scalar value, it will throw error. So, it will be useful for trouble shooting. On the other hand SELECT will assign one of the value, most probably the last value to the variable. So, troubleshooting will be harder.
Also, when assigning values from a select query, if the query does not return any value, SET will assign NULL. On the other hand SELECT will retain the old value. This makes the troubleshooting harder with SELECT.
If error handling and troubleshooting is important to you, then go for SET.
User Friendliness
Another difference between SET and SELECT comes in the user friendliness. Compared to SELECT, SET is more user friendly. This is because,even though it takes multiple SET statements to assign multiple variables, it gives your code readability and ease of troubleshooting. On the other hand, SELECT has the capability of assigning multiple variables in a single statement. Still it will make the code less readable and troubleshooting harder.
SELECT vs SET Comparison Table
Now we see the overall differences between SET and SELECT.
| # |
SET |
SELECT |
|---|---|---|
| 1 | Can assign only one variable in a statement. | Can assign values to multiple variables in a single statement. |
| 2 | There is no difference in performance for assigning only one variable. | No difference for one variable. |
| 3 | Little slow in performance when assigning multiple variables. | Faster than SET, because of the capability of assigning multiple variables at the same time. |
| 4 | ANSI standard and Microsoft’s recommended option to assign a variable. | Non-ANSI option. |
| 5 | When assigning values from a select query, if the query returns more than one scalar value, it will throw error. So, it will be useful for trouble shooting. | If the query returns multiple values, SELECT will assign one of the value, most probably the last value to the variable. So, troubleshooting will be harder. |
| 6 | When assigning values from a select query, if the query does not return any value, SET will assign NULL. | In such situations, SELECT will retain the old value. So, this may also makes the troubleshooting harder. |
Conclusion
Based on the above comparisons, SELECT has a slight edge over SET in performance while assigning multiple values. However if you consider ANSI standard, user friendliness, error handling and troubleshooting, then using SET is the best way to go.
I, personally prefer SET for variable assignment and use them often.
Reference
- Assigning variables using SET at Microsoft Docs.