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.

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 (http://blog.SQLAuthority.com)

About these ads

12 thoughts on “SQL SERVER – SELECT vs. SET Performance Comparison

  1. 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

  2. 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

  3. Pingback: SQL SERVER – Tricks to Replace SELECT * with Column Names – SQL in Sixty Seconds #017 – Video « SQL Server Journey with SQL Authority

  4. 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

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s