SET vs. SELECT when assigning values to variables

There are 2 ways of assigning a value to a variable, they are SET and the SELECT statements.

SET

1. By using SET, we can only assign one variable at a time.
   
   Example:
   
   1. DECLARE @COUNT INT
   2. DECLARE @INDEX INT
   4. SET @COUNT=1
   5. SET @INDEX=2
2. If assigning a value using SET from a query returns no result, then SET will assign a NULL value to the variable.
   
   Example:
   
   1. DECLARE @EmpID VARCHAR(5)
   2. SET @EmpID = '123'
   3. SET @EmpID = (SELECT [EmpID]
   4. FROM [dbo].[Employee]
   5. WHERE [EmpID] = '321')
   6. --Assume that there is no record with empid 321 in employee table
   7. SELECT @EmpID
   9. Result:
   10. NULL
   Result:
   
   NULL
   
   
3. If assigning a value using SET returns more than one value, SET will give an error.
   
   Example:
   
   1. DECLARE @EmpName VARCHAR(50)
   2. SET @EmpName = 'Raj'
   3. SET @EmpName = (SELECT [EmpName]
   4. FROM [dbo].[Employee]
   5. WHERE [EmpName] = 'Tej')
   6. --Assume that there is multiple records with empname Tej in employee table
   7. SELECT @EmpName
   Result:
   
   Msg 512, Level 16, State 1, Line 3
   
   Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SELECT

1. By using SELECT we can assign values to more than one variable at a time.
   
   Example:
   
   1. DECLARE @COUNT INT
   2. DECLARE @INDEX INT
   4. SELECT @COUNT=1 , @INDEX=2
2. When assigning a value using SELECT from a query and the query returns no result, SELECT will not assign any value to the variable and therefore no change in the value of the variable.
   
   Example:
   
   1. DECLARE @EmpID VARCHAR(5)
   2. SET @EmpID = '123'
   3. SELECT @EmpID = [EmpID]
   4. FROM [dbo].[Employee]
   5. WHERE [EmpID] = '321'
   6. --Assume that there is no record with empid 321 in employee table
   7. SELECT @EmpID
   8. 
      
   Result:
   
   10. 123
   
   
3. When assigning a value using SELECT from a query that returns more than one value, SELECT will assign the last value returned by the query.
   
   Example:
   
   1. DECLARE @EmpName VARCHAR(50)
   2. SET @EmpName = 'Raj'
   3. SELECT @EmpName = [EmpName]
   4. FROM [dbo].[Employee]
   5. WHERE [EmpName] = 'Tej'
   6. --Assume that there is multiple records with empname Tej in employee table
   7. SELECT @EmpName
   Result:
   
   Tej