Usage:
SELECT : Designed to return data.
SET : Designed to assign values to local variables.
While testing the performance of the following two scripts in query analyzer, interesting results are discovered.
SET @foo1 = 1;
SET @foo2 = 2;
SET @foo3 = 3;
SELECT
@foo1 = 1,
@foo2 = 2,
@foo3 = 3;
While comparing their performance in loop SELECT statement gives better performance then SET. In other words, SET is slower than SELECT. The reason is that each SET statement runs individually and updates on values per execution, whereas the entire SELECT statement runs once and update all three values in one execution.
SET is the ANSI standard for variable assignment, SELECT is not. SET can only assign one variable at a time, SELECT can make multiple assignments at once – that gives SELECT slight speed advantage over SET. If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all keeping the variable unchanged.
Reference : Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
i never thought about that. but that’s interesting.
i take my hat off to you for your delicate curiosity.
thanks pinaldave~
SET vs SELECT – Sql server
We always get confused between SELECT and SET when assigning values to variables, and make mistakes. Here in this article, I will try to highlight all the major differences between SET and SELECT, and things you should be aware of, when using either SET or SELECT.
Yes, I also use any of them at any time without thinking for any performance.
But this is great !!
Note that if you use subqueries in your SELECT examples, then you get the advantage of the multi-value assignment, with the same null value and error behaviour as SET.
I.E. SELECT @a=a, @b=b, @c=c FROM (SELECT a,b,c FROM x) A
Excellent!!! Thanks for being there always
excellent ! thanks a lot
I understand one of theirs differences when I were looking for raising error from transactions and do something in catch , there I couldn’t do anything in catch so I used GOTO structure to handle errors,and there I found using SELECT to raise @@ERROR like this
SELECT @ERROR_NUMBER = @@ERROR ,@ERROR_LINE=@@ROWCOUNT
IF @ERROR_NUMBER != 0 GOTO HANDLE_ERROR
make no execution against SET, so we can find out whether error occurred or no while if we use SET here it would make another error and we can not fount whether error occurred or no
this was my experience for some one who need,,just this.
thanks
You may also read this to know more about dealing with multiple results
This is regarding last point (When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all keeping the variable unchanged.):
If I understood correct, below query should return
NULL and 200
DECLARE
@set int = 100
,@select int = 200;
SET @set = (SELECT TOP 1 DepartmentID FROM HumanResources.Department WHERE DepartmentID = -1);
SELECT @select = (SELECT DepartmentID FROM HumanResources.Department WHERE DepartmentID = -1)
SELECT @set as [set],@select as [select];
But it returns NULL and NULL!
If I am not wrong, can you please provide more detail.
Thanks
Read this post
what is the exact difference between Table variable and Temp Table.
Is there a way to declare and set Global variable in SQL Server? I have tried storing it in a table, but I have seen when multiple queries select that variable from that table, it gives performance problems. I know in Oracle we have, but do we have any thing similar in SQL Server as well?