SQL Server Variable Assignment - SET or SELECT?

Declaration variables and assigning values in different ways is something we see as a very common code in modules like stored procedures and functions. It is possible to assign a value at the declaration or a value can be assigned after the declaration either using SET or SELECT. A question raised on it, what would be the best and which gives better performance, when assigning the value using SET or SELECT. This is what I explained;

Let's see the differences one by one;

1. Only one variable can be set with a value with a single SET statement but with the SELECT statement, multiple variables can be set with values using a single SELECT statement.

view plainprint?

1. -- Declaring variables without assigning values  
2. DECLARE @Variable01 int;  
3. DECLARE @Variable02 int;  
4.   
5. -- New two SET statements for assigning values  
6. SET @Variable01 = 100; SET @Variable02 = 200;   
7. SELECT @Variable01, @Variable02;  
8.   
9. -- Assigning values using a single SELECT  
10. SELECT @Variable01 = 300, @Variable02 = 300;  
11. SELECT @Variable01, @Variable02;  
12. GO  

-- Declaring variables without assigning values
DECLARE @Variable01 int;
DECLARE @Variable02 int;

-- New two SET statements for assigning values
SET @Variable01 = 100; SET @Variable02 = 200; 
SELECT @Variable01, @Variable02;

-- Assigning values using a single SELECT
SELECT @Variable01 = 300, @Variable02 = 300;
SELECT @Variable01, @Variable02;
GO

2. Both SET and SELECT support assigning values using a query. Just like the number (1), SELECT can be used for setting multiple variables.

view plainprint?

1. USE WideWorldImporters;  
2. GO  
3.   
4. -- Declaring variables without assigning values  
5. DECLARE @Variable01 int;  
6. DECLARE @Variable02 int;  
7.   
8. -- Assigning a value using a query, this works as expected  
9. SET @Variable01 = (SELECT COUNT(*) FROM Sales.Customers);   
10. SELECT @Variable01;  
11.   
12. -- Assigning values using a query, this works as expected  
13. SELECT @Variable01 = COUNT(*), @Variable02 = AVG(CustomerID) FROM Sales.Customers  
14.   
15. SELECT @Variable01, @Variable02;  
16. GO  

USE WideWorldImporters;
GO

-- Declaring variables without assigning values
DECLARE @Variable01 int;
DECLARE @Variable02 int;

-- Assigning a value using a query, this works as expected
SET @Variable01 = (SELECT COUNT(*) FROM Sales.Customers); 
SELECT @Variable01;

-- Assigning values using a query, this works as expected
SELECT @Variable01 = COUNT(*), @Variable02 = AVG(CustomerID) FROM Sales.Customers

SELECT @Variable01, @Variable02;
GO

3. When assigning values using a query, if the query returns more than one record, SET returns an error whereas SELECT takes the first record and assign the value to the variable.

view plainprint?

1. USE WideWorldImporters;  
2. GO  
3.   
4. -- Declaring variables without assigning values  
5. DECLARE @Variable01 int;  
6. DECLARE @Variable02 int;  
7.   
8. -- Assigning a value using a query, this query returns more than one value  
9. -- , hence SET throws an error  
10. SET @Variable01 = (SELECT CustomerID FROM Sales.Customers);   
11. SELECT @Variable01;  
12.   
13. -- Assigning a value using a query, this query returns more than one value  
14. -- , but SELECT takes the first value without throwing an error  
15. SELECT @Variable01 = CustomerID FROM Sales.Customers  
16.   
17. SELECT @Variable01, @Variable02;  
18. GO  

USE WideWorldImporters;
GO

-- Declaring variables without assigning values
DECLARE @Variable01 int;
DECLARE @Variable02 int;

-- Assigning a value using a query, this query returns more than one value
-- , hence SET throws an error
SET @Variable01 = (SELECT CustomerID FROM Sales.Customers); 
SELECT @Variable01;

-- Assigning a value using a query, this query returns more than one value
-- , but SELECT takes the first value without throwing an error
SELECT @Variable01 = CustomerID FROM Sales.Customers

SELECT @Variable01, @Variable02;
GO

4. When assigning values using a query, if the query returns no record, NULL will be set to the variable with SET statement but SELECT statement will keep the old value without changing.

view plainprint?

1. USE WideWorldImporters;  
2. GO  
3.   
4. -- Declaring variables, assigning values  
5. DECLARE @Variable01 int = 0;  
6. DECLARE @Variable02 int = 0;  
7.   
8. -- Assigning a value using a query  
9. -- This query does not return any record, hence variable becomes NULL  
10. SET @Variable01 = (SELECT CustomerID FROM Sales.Customers WHERE BuyingGroupID = 100 );   
11.   
12. -- Assigning a value using a query  
13. -- This query does not return any record, but initial value will not be replaced with a NULL  
14. SELECT @Variable02 = CustomerID FROM Sales.Customers WHERE BuyingGroupID = 100;  
15.   
16. SELECT @Variable01, @Variable02;  

USE WideWorldImporters;
GO

-- Declaring variables, assigning values
DECLARE @Variable01 int = 0;
DECLARE @Variable02 int = 0;

-- Assigning a value using a query
-- This query does not return any record, hence variable becomes NULL
SET @Variable01 = (SELECT CustomerID FROM Sales.Customers WHERE BuyingGroupID = 100 ); 

-- Assigning a value using a query
-- This query does not return any record, but initial value will not be replaced with a NULL
SELECT @Variable02 = CustomerID FROM Sales.Customers WHERE BuyingGroupID = 100;

SELECT @Variable01, @Variable02;

5. One more point, remember that SET is ANSI standard for assigning variables but SELECT is not.