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


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

5 comments:

Ben said...

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

Anonymous said...

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

Suprotim Agarwal said...

Thanks Boyan and Bart for the informative comments..

Anonymous said...

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)

DK said...

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