9.01.2012

SET vs SELECT in SQL Server

Difference between SET and SELECT in SQL Server

S.No SET SELECT
1
Is it ANSI Standard ?
SET is ANSI Standard for value assignment to variables.
Is it ANSI Standard ?
SELECT is Non-ANSI Standard for value assignment to variables.
2
Variable Assignment:
SET can be used to assign value to one variable at a time.

DECLARE @i INT,
@j INT,
@k INT
SET @i = 10,@j = 20,@k = 30

It gives error:

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

Variable Assignment:
SELECT can be used to assign values to multiple variables in a single SELECT statement.

The below query using SELECT is valid:
1
2
3
4
5
DECLARE @i INT,
@j INT,
@k INT
SELECT @i = 10,@j = 20,@k = 30

Output:
Command(s) completed successfully.

The below query using SET is not valid:
1
2
3
4
5
3
Behaviour of SET when query returns more then one value:
When assigning from a query that returns more than one value, SET will fail with an error.

The below query using set will fail:
1
2
3
DECLARE @i INT
SET @i = (SELECT n FROM (VALUES (10),(20),(30)) AS List(n))

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.

Behaviour of SELECT when query returns more then one value:
When assigning from a query that returns more than one value, SELECT will assign the last value returned by the query and hide the fact that the query returned
more than one row.

The below query using select will execute successfully:
1
2
3
4
5
DECLARE @i INT
SELECT @i = n FROM (VALUES (10),(20),(30)) AS List(n)
SELECT @i

Output:

30

(1 row(s) affected)
4
Behaviour of SET when query does not return any rows:
If the variable is initially assigned a value following is the behavior of variable assignment for SET,

Assigns null if the query does not return any rows.

The output of the below statement will be NULL
1
2
3
4
5
6
7
DECLARE @i INT
SET @i = 1
SET @i = (SELECT n FROM (VALUES (10),(20),(30)) AS List(n) WHERE 1=2)
SELECT @i

Output:

NULL

(1 row(s) affected)
Behaviour of SELECT when query does not return any rows:
If the variable is initially assigned a value following is the behavior of variable assignment for 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
1
2
3
4
5
6
7
DECLARE @i INT
SET @i = 1
SELECT @i = n FROM (VALUES (10),(20),(30)) AS List(n) WHERE 1=2
SELECT @i

Output:

1

(1 row(s) affected)
5
Performance:
Set does not provide better performance over select when used for assigning values to multiple variables at the same time.

Assigning values to multiple variables using SET:
1
2
3
4
5
6
7
DECLARE @i INT,
@j INT,
@k INT
SET @i = 10
SET @j = 20
SET @k = 30
Performance:
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:
1
2
3
4
5
DECLARE @i INT,
@j INT,
@k INT
SELECT @i = 10,@j = 20,@k = 30
6
When to use ?
Following are few scenarios for using SET

1. If we are required to assign a single value directly to variable and no query is involved to fetch value.
2. NULL assignments are expected (NULL returned in result set)
3. Standards are meant to be follow for any planned migration
4. Non scalar results are expected and are required to be handled
When to use ?
Using SELECT is efficient and flexible in the following few cases.

1. Multiple variables are being populated by assigning values directly
2. Multiple variables are being populated by single source (table , view)
3. Less coding for assigning multiple variables
4. Use this if we need to get @@ROWCOUNT and @ERROR for last statement executed


No comments:

Post a Comment