SQL Server: Truth about assigning variables using SET versus SELECT

In this post, I am sharing the major difference between SET and SELECT, which we are using for assigning a value to the Variable of SQL Server.

What I have found is, the database people are always confused to use SET or SELECT for assigning a value to the Variable and which one is faster ?

SET is the ANSI standard and SELECT is not the ANSI standard.

Using SET we can only assign a scalar value and if the query is returning multiple values, SET will raise an error.
In contrast, SELECT has never raised an error if the query is returning multiple values and this is something wrong because out of this different value its stored last value.

Using SET we can assign one variable at a time, using SELECT we can assign multiple variable at once.

There are not a major difference in speed and performance, but SELECT is slightly faster than SET because it can perform multiple assignment at once.

As per the ANSI standard, If there is no value returned, SET will assign NULL and SELECT will not assign any value and variable will not be changed from its previous value.

For this Difference, I have prepared one small demonstration.

First, Create table with Sample Data:

Assigning variable using SET:

Assigning variable using SELECT:

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of