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
5 comments:
A shorter variation of:
SELECT @CID = (Select CID from Customers)
is
SELECT @CID = CID from Customers
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
Thanks Boyan and Bart for the informative comments..
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)
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
Post a Comment