SET VS SELECT - When to use what?

Let us observe the following two queries :

DECLARE @CID int
SET @CID =(Select CID from Customers)
SELECT @CID = (Select CID from Customers)

Both work well as far as a single row is being returned. When multiple rows are returned, the SET fails , whereas SELECT is the way to go. If the query returns nothing, SET assigns NULL as the value of the variable, whereas SELECT ignores it. SELECT is also capable of making multiple assignments.

Usually when a single value is to be assigned to a variable, go in for SET. However if you are setting the value using a query, as shown above, then its better to go in for SELECT as the rows returned 'could' be more than one, which can be handled by SELECT but not SET.

Another difference is that SET is ANSI standard, SELECT is not.

As a rule of thumb, I go ahead with SELECT when I have to set multiple variables. Else if I have to only set one variable, I am better off with SET.

Here's a good read about SET VS SELECT performance
http://www.sqlmag.com/Articles/ArticleID/94555/94555.html
at

5 comments:

  1. BenApril 25, 2008 at 5:49 AM

    A shorter variation of:
    SELECT @CID = (Select CID from Customers)
    is
    SELECT @CID = CID from Customers

    ReplyDelete
  2. AnonymousApril 27, 2008 at 8:58 AM

    Multiple assignments is by far the biggest reason to use select over set. It gives you a huge performance gain if you are setting a lot of variables for a big sproc/script

    declare @test int, @test2 int;
    set @test = 1, @test2 = 2; --doesn't work
    select @test = 1, @test2 = 2; -- works

    ReplyDelete
  3. Suprotim AgarwalApril 27, 2008 at 11:02 AM

    Thanks Boyan and Bart for the informative comments..

    ReplyDelete
  4. AnonymousApril 12, 2010 at 7:35 AM

    Actually,
    SET @CID =(Select CID from Customers)
    SELECT @CID = (Select CID from Customers)
    will behave identically - both will fail for multiple rows.

    SELECT will behave as you described when you don't use a subquery (i.e. SELECT @CID = CID from Customers)

    ReplyDelete
  5. DKJune 23, 2010 at 11:06 PM

    SET @CID =(Select CID from Customers)
    SELECT @CID = (Select CID from Customers)
    Both of these statements will fail if there are multiple rows in the result.

    Following is the actual syntax if query returns multiple results

    SELECT @CID = CID from Customers

    ReplyDelete