SET vs SELECT in SQL Server

SET vs SELECT is a common topic of discussion among SQL geeks and one of the most asked interview question. SET is used to assign a value to a variable and SELECT is used to assign a value or to select value from a variable/table/view etc.

Let’s see the usage of SET and SELECT in different scenarios.

Assigning scalar values

-- Query 1: Assigning scalar values 
Declare 
	@set INT,
	@select INT

SET @set=1
SELECT @select=1

SELECT @set AS 'Set', @select AS 'Select'
GO

In the above query both SET and SELECT are used to assign a value to a variable.

Assigning values to multiple parameters

--Query 2: Assigning values to multiple parameters
DECLARE 
@s1 INT, 
@s2 INT

-- 2 SET statements 
SET @s1=1
SET @s2=2

-- 1 Select statement
SELECT @s1=1, @s2=2

SET statement can assign value to a variable at a time. In order to assign values to two different variable two different SET statements are required. SELECT statement can assign values to multiple variables in a single go, as shown in above query. In this case, the SELECT statement performs better than SET.

Assigning values from a query

In below snapshot, SELECT statement is used to assign value to a variable from a select query. The SELECT statement assigns last value from the result set to the variable if the select query returns more than one result set.

1_SET vs SELECT in SQL Server

The SET statement errors out if the query returns more than one result set as shown below.

2_SET vs SELECT in SQL Server

The SELECT statement doesn’t changes variable value if the query returns 0 rows. On the contrary, the SET statement sets variable to NULL, overriding previous value if the query returns 0 rows. This is shown in below snapshot.

3_SET vs SELECT in SQL Server

 

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Leave a Reply

Your email address will not be published. Required fields are marked *