SET versus SELECT when assigning variables?

Question

What are the differences between the SET and SELECT statements when assigning variables in T-SQL?

Solution 1

Quote, which summarizes from this article:

1. SET is the ANSI standard for variable assignment, SELECT is not.
2. SET can only assign one variable at a time, SELECT can make multiple assignments at once.
3. 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 (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)
4. 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 (so the variable will not be changed from its previous value)
5. As far as speed differences - there are no direct differences between SET and SELECT. However SELECT's ability to make multiple assignments in one shot does give it a slight speed advantage over SET.

Solution 2

I believe SET is ANSI standard whereas the SELECT is not. Also note the different behavior of SET vs. SELECT in the example below when a value is not found.

declare @var varchar(20)
set @var = 'Joe'
set @var = (select name from master.sys.tables where name = 'qwerty')
select @var /* @var is now NULL */

set @var = 'Joe'
select @var = name from master.sys.tables where name = 'qwerty'
select @var /* @var is still equal to 'Joe' */

Solution 3

When writing queries, this difference should be kept in mind :

DECLARE @A INT = 2

SELECT  @A = TBL.A
FROM    ( SELECT 1 A ) TBL
WHERE   1 = 2

SELECT  @A
/* @A is 2*/

---------------------------------------------------------------

DECLARE @A INT = 2

SET @A = ( 
            SELECT  TBL.A
            FROM    ( SELECT 1 A) TBL
            WHERE   1 = 2
         )

SELECT  @A
/* @A is null*/

Solution 4

Aside from the one being ANSI and speed etc., there is a very important difference that always matters to me; more than ANSI and speed. The number of bugs I have fixed due to this important overlook is large. I look for this during code reviews all the time.

-- Arrange
create table Employee (EmployeeId int);
insert into dbo.Employee values (1);
insert into dbo.Employee values (2);
insert into dbo.Employee values (3);

-- Act
declare @employeeId int;
select @employeeId = e.EmployeeId from dbo.Employee e;

-- Assert
-- This will print 3, the last EmployeeId from the query (an arbitrary value)
-- Almost always, this is not what the developer was intending. 
print @employeeId; 

Almost always, that is not what the developer is intending. In the above, the query is straight forward but I have seen queries that are quite complex and figuring out whether it will return a single value or not, is not trivial. The query is often more complex than this and by chance it has been returning single value. During developer testing all is fine. But this is like a ticking bomb and will cause issues when the query returns multiple results. Why? Because it will simply assign the last value to the variable.

Now let's try the same thing with SET:

 -- Act
 set @employeeId = (select e.EmployeeId from dbo.Employee e);

You will receive an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

That is amazing and very important because why would you want to assign some trivial "last item in result" to the @employeeId. With select you will never get any error and you will spend minutes, hours debugging.

Perhaps, you are looking for a single Id and SET will force you to fix your query. Thus you may do something like:

-- Act
-- Notice the where clause
set @employeeId = (select e.EmployeeId from dbo.Employee e where e.EmployeeId = 1);
print @employeeId;

Cleanup

drop table Employee;

---

In conclusion, use:

• SET: When you want to assign a single value to a variable and your variable is for a single value.
• SELECT: When you want to assign multiple values to a variable. The variable may be a table, temp table or table variable etc.

View more solutions

Comments

[A-K]

I did not downvote, but the following is not quite correct: "As far as speed differences - there are no direct differences between SET and SELECT". If you assign multiple values in one slect, that can be much faster that via maultiple sets. Google up "Assigning multiple variables with one SELECT works faster"

[OMG Ponies]

@AlexKuznetsov: The sentence afterwards says exactly that.

[A-K]

@OMG Ponies: It can be 10 times faster or more, so I am not sure if it is "slight speed advantage".

[Gennady Vanin Геннадий Ванин]

+1 It is better to run once in order to understand, check, play, memorize that to just read but other answers are just text

[Zack]

If you actually used select @var = (select name from master.sys.tables where name = 'qwerty') you would get @var as null. The example you are giving is not the same query.

[Zack]

You are seeing different results, because you are using a different query... If your point is to show that the same query behaves differently when you use set as opposed to select then why are you changing what's on the right side of the = symbol in each example? If you use the same thing you will get the same result.

[Joe Stefanelli]

@Zack What are you seeing different on the right of the =? They both say name from master.sys.tables where name = 'qwerty'

[Zack]

You have (select name from master.sys.tables where name = 'qwerty') for one, and name from master.sys.tables where name = 'qwerty' for the other... do you not see that?

[Joe Stefanelli]

@Zack: Each is the correct syntax for what I am attempting to demo; the difference between using SET vs. SELECT to assign a value to a variable when the underlying query returns no results.

[Zack]

(select name from master.sys.tables where name = 'qwerty') is a scalar subquery, and name from master.sys.tables where name = 'qwerty' is a simple query. The two different expressions are not supposed to produce the same results, though it seems you are implying that they should. If you are trying to say the SET and SELECT keywords have different implementations, you should not be using two different expressions in your examples. msdn.microsoft.com/en-us/library/ms187330.aspx

[MikeTeeVee]

Especially when using a While-Loop, I have seen HUGE performance gains by setting/re-initializing all my variables using one-Select vs. many-Set's. I can also consolidate my Variable-Logic in a Select to all run at once too: Example: SELECT @Int = @Int + 1, @Int = @Int + 1, if @Int started as 0, it then ends as 2. This can be very useful when doing successive string-manipulations.

[SimplyInk]

very nice, succinct

[youcantryreachingme]

Interesting discussion about performance difference. If setting multiple values via select is faster (to code and execute) then one failsafe way to avoid point 4 (your variable value failing to change if the query returns null) is to explicitly set your variable to null prior to the select. Once you factor that in, how do the two compare for performance? (Side note: I don't understand the rationale for select not setting your variable to null in the event a query returns null. When would you ever want that?)