SQLSERVERLEARNER

SET and SELECT statements are similar in many ways but what are the major differences between them?

This post explains the main differences between set and select statements.

Select Statement:
[sql]
DECLARE @i INT
SELECT @i = 10
[/sql]

SET Statement:
[sql]
DECLARE @i INT
SET @i = 10
[/sql]

1) SET is ANSI Standard for value assignment to variables but SELECT is not an ANSI Standard for variable assignment.

2) SET can be used to assign value to one variable at a time but select can be used to assign values to multiple variables in a single select statement.

The below query using SELECT is valid:
[sql]
DECLARE @i INT,
@j INT,
@k INT

SELECT @i = 10,@j = 20,@k = 30
[/sql]
Output:
Command(s) completed successfully.

The below query using SET is not valid:
[sql]
DECLARE @i INT,
@j INT,
@k INT

SET @i = 10,@j = 20,@k = 30
[/sql]
It gives error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ‘,’.

3) When an output of a query is used to assign values to a variable then SET Statement would fail and give an error if multiple rows are returned by the query but the select statement would assign the last result of the query to the the variable.

The below query using select will execute successfully:
[sql]
DECLARE @i INT

SELECT @i = n FROM (VALUES (10),(20),(30)) AS List(n)

SELECT @i
[/sql]
Output:
———–
30

(1 row(s) affected)

The below query using set will fail:
[sql]
DECLARE @i INT

SET @i = (SELECT n FROM (VALUES (10),(20),(30)) AS List(n))
[/sql]

Error:

Msg 512, Level 16, State 1, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.

4) If the variable is initially assigned a value following is the behavior of variable assignment using set and select.
Set – Assigns null if the query does not return any rows.
Select – Retains the initially assigned value and does not assign null if the query does not return any rows.

The output of the below statement will be 1
[sql]
DECLARE @i INT

SET @i = 1

SELECT @i = n FROM (VALUES (10),(20),(30)) AS List(n) WHERE 1=2

SELECT @i
[/sql]

Output:

———–
1

(1 row(s) affected)

The output of the below statement will be NULL
[sql]
DECLARE @i INT

SET @i = 1

SET @i = (SELECT n FROM (VALUES (10),(20),(30)) AS List(n) WHERE 1=2)

SELECT @i
[/sql]

Output:
———–
NULL

(1 row(s) affected)

5) Select has better performance over set when used for assigning values to multiple variables at the same time.

Assigning values to multiple variables using Select:
[sql]
DECLARE @i INT,
@j INT,
@k INT

SELECT @i = 10,@j = 20,@k = 30
[/sql]

Assigning values to multiple variables using SET:
[sql]
DECLARE @i INT,
@j INT,
@k INT

SET @i = 10
SET @j = 20
SET @k = 30
[/sql]

6) There are many other differences with respect to syntax and uses of SET and Select.
This post is mainly tuned for value assignment to variables using SET and Select.