SQL SERVER – SELECT vs. SET Performance Comparison

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.

Solarwinds

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)

Solarwinds
, ,
Previous Post
SQL SERVER – Difference Between Unique Index vs Unique Constraint
Next Post
SQL SERVER – 2005 Locking Hints and Examples

Related Posts

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~

    Reply
  • Sanjeev sharma
    April 20, 2008 12:39 pm

    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.

    Reply
  • Yes, I also use any of them at any time without thinking for any performance.

    But this is great !!

    Reply
  • 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

    Reply
  • Excellent!!! Thanks for being there always

    Reply
  • 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

    Reply
  • You may also read this to know more about dealing with multiple results

    Reply
  • Pulkit Choudhary
    June 6, 2012 10:09 pm

    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

    Reply
  • what is the exact difference between Table variable and Temp Table.

    Reply
  • 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?

    Reply

Leave a Reply

Menu