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

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

Assigning values to multiple parameters

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

 

Regards

Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook