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:

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of