T-SQL: SET vs SELECT when assigning variables

Monday, March 1, 2004 9:33 AM Filed under:

SQL/Data

T-SQL: SET vs SELECT when assigning variables

Do you know what the difference is between using SET and SELECT when assigning varaibles in T-SQL? Well, there are some differences. I came across a great article by Narayana Vyas Kondreddi from the UK that describes the difference between the two.

The article is well worth the read, but here are the main points:

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.
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 (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)
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 (so the variable will not be changed from it's previous value)
As far as speed differences - there are no direct differences between SET and SELECT. However SELECT's ability to make multiple assignments in one shot does give it a slight speed advantage over SET.

Take a look at the complete article to see Vyas' complete tests to get the entire picture:
[http://vyaskn.tripod.com/differences_between_set_and_select.htm]

Comments

Jason 1/1/2005 11:09 PM

Thank you!
Gary 11/8/2005 3:00 AM

Thanks for this post this is exactly what I was looking for. It seems you and I had the same Idea for naming of Blogs :-)
Sean 3/15/2006 10:59 AM

Just got a new job as SQL Server DBA...very helpful....
Zahid Nawaz Kashmala Fatima 5/19/2006 7:38 AM

Good worthy Article
Rajasekhar 7/18/2006 7:24 AM

Looking for the same, Thanks for the post
Gomathi 7/20/2006 12:56 AM

Thanks for your information and article link.
Alex 8/11/2006 1:39 PM

Is this also the case for SQL 2000, or just 2005?
Ryan Farley 8/11/2006 1:44 PM

Alex,

This is the case for T-SQL, makes no difference if it is 2000 or 2005.

-Ryan
simon 2/15/2007 7:20 AM

-- Sorry but the following code shows that
-- (A) SET and SELECT are IDENTICAL IN THEIR BEHAVIOUR
-- (B1) When assigning from a query IF THE QUERY RETURNS MULTIPLE ROWS the variable is unchanged (and an error occurs)
-- (B1) When assigning from a query IF THE QUERY RETURNS NO ROWS the variable is set to NULL and no error occurs

alter procedure
smSetAndSelectTest as

--exec smSetAndSelectTest
declare @VAR varchar(80)

--see http://ryanfarley.com/blog/archive/2004/03/01/390.aspx

--He says

-- 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
-- (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)"
-- 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
-- (so the variable will not be changed from it's previous value

-- The following code shows that
-- (A) SET and SELECT are IDENTICAL IN THEIR BEHAVIOUR
-- (B1) When assigning from a query IF THE QUERY RETURNS MULTIPLE ROWS the variable is unchanged (and an error occurs)
-- (B1) When assigning from a query IF THE QUERY RETURNS NO ROWS the variable is set to NULL and no error occurs

print isNULL(@var,'@var is NULL')
SET @var ='SET and SELECT test'
print isNULL(@var,'@var is NULL')

SET @var ='(1a) SELECT NULL value assignment test'
print isNULL(@var,'@var is NULL')
SELECT @var = NULL
print isNULL(@var,'@var is NULL')

SET @var ='(1b) SET NULL value assignment test'
print isNULL(@var,'@var is NULL')
SET @var = NULL
print isNULL(@var,'@var is NULL')

print '-- SELECT @VAR is NOT CHANGED ON MULTIROW RETURN -------------------------'
SET @var ='(2a) Select Multiple rows value assignment test'
print isNULL(@var,'@var is NULL')
SELECT @var = (select fin_year from tikkadev.olapdb.dbo.annual_indicators where fin_year = '2006')
print isNULL(@var,'@var is NULL')

print '-- SET @VAR is NOT CHANGED ON MULTIROW RETURN -------------------------'
SET @var ='(2b) Set Multiple rows value assignment test'
print isNULL(@var,'@var is NULL')
SET @var = (select fin_year from tikkadev.olapdb.dbo.annual_indicators where fin_year = '2006')
print isNULL(@var,'@var is NULL')

SET @var ='(3a) Single row SELECT assignation best practice'
print isNULL(@var,'@var is NULL')
SELECT @var = (select max(fin_year) from tikkadev.olapdb.dbo.annual_indicators where fin_year = '2006')
print isNULL(@var,'@var is NULL')

SET @var ='(3b) Single row SET assignation best practice'
print isNULL(@var,'@var is NULL')
SET @var = (select max(fin_year) from tikkadev.olapdb.dbo.annual_indicators where fin_year = '2006')
print isNULL(@var,'@var is NULL')

print '-- SET AND SELECT @VAR is NULL WHERE NO ROWS RETURNED -----------------'
SET @var ='(4a) Select value assignment test (no rows returned)'
print isNULL(@var,'@var is NULL')
SELECT @var = (select fin_year from tikkadev.olapdb.dbo.annual_indicators where 1=2)
print isNULL(@var,'@var is NULL')

SET @var ='(4b) Set value assignment test (no rows returned)'
print isNULL(@var,'@var is NULL')
SET @var = (select fin_year from tikkadev.olapdb.dbo.annual_indicators where 1=2)
print isNULL(@var,'@var is NULL')

SET @var ='(5a) Best Practice value assignment test (no rows returned)'
print isNULL(@var,'@var is NULL')
SELECT @var = (select max(fin_year) from tikkadev.olapdb.dbo.annual_indicators where 1=2)
print isNULL(@var,'@var is NULL')

SET @var ='(5b) Best Practice value assignment test (no rows returned)'
print isNULL(@var,'@var is NULL')
SET @var = (select max(fin_year) from tikkadev.olapdb.dbo.annual_indicators where 1=2)
print isNULL(@var,'@var is NULL')
{ public virtual blog; } 2/15/2007 10:20 AM
Simon (data_wizard223300@hotmail 2/15/2007 7:24 AM

-- Here is the output

@var is NULL
SET and SELECT test
(1a) SELECT NULL value assignment test
@var is NULL
(1b) SET NULL value assignment test
@var is NULL
-- SELECT @VAR is NOT CHANGED ON MULTIROW RETURN -------------------------
(2a) Select Multiple rows value assignment test
Server: Msg 512, Level 16, State 1, Procedure smSetAndSelectTest, Line 44
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
(2a) Select Multiple rows value assignment test
-- SET @VAR is NOT CHANGED ON MULTIROW RETURN -------------------------
(2b) Set Multiple rows value assignment test
Server: Msg 512, Level 16, State 1, Procedure smSetAndSelectTest, Line 50
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
(2b) Set Multiple rows value assignment test
(3a) Single row SELECT assignation best practice
2006
(3b) Single row SET assignation best practice
2006
-- SET AND SELECT @VAR is NULL WHERE NO ROWS RETURNED -----------------
(4a) Select value assignment test (no rows returned)
@var is NULL
(4b) Set value assignment test (no rows returned)
@var is NULL
(5a) Best Practice value assignment test (no rows returned)
@var is NULL
(5b) Best Practice value assignment test (no rows returned)
@var is NULL
Joon 5/23/2007 1:28 AM

Your multi-row errors are because your statement was:

SELECT @var = (select fin_year from tikkadev.olapdb.dbo.annual_indicators where fin_year = '2006')

And not

select @var = fin_year from tikkadev.olapdb.dbo.annual_indicators where fin_year = '2006'

This is the situation that Vyas was talking about in the link article, and is the one that will assign the first or last (I cannot remember which) value
host 6/13/2007 11:29 AM

Thats alot of code. Give me some time to assimilate all that
Jshirk 7/12/2007 9:44 AM

i love this spiffy split function, which takes a string and returns a table variable. However, I have not been successful at using it to build a table of all words the function returns for all rows in a table. I tried WHILE EXISTS(Select...) but have not gotten it to work.

basic idea (which is not syntactically correct):

while exists (select @str=description from item)
begin
insert @words
select fn_split(@str,'@delim')
end

Any joy? Thanks!
Jshirk 7/13/2007 7:43 AM

FYI here is my solution; it works!

declare @words TABLE (word varchar(40))
declare @s varchar(80)
DECLARE Description_Cursor CURSOR
FOR
SELECT description FROM item
OPEN Description_Cursor
FETCH FROM Description_Cursor INTO @s
insert @words select word from dbo.GETALLWORDS(@s, ' ,."') --ORDER BY word

FETCH NEXT FROM Description_Cursor INTO @s
WHILE @@Fetch_Status = 0
BEGIN
insert @words select word from dbo.GETALLWORDS(@s, ' ,."') --ORDER BY word
FETCH NEXT FROM Description_Cursor INTO @s
END
CLOSE Description_Cursor
DEALLOCATE Description_Cursor
select distinct * from @words order by word
Davis 10/1/2007 10:50 AM

You should really create a function to do this and keep this function in your stable of code. Found this one on the web. I did a cursory review of it and think it is sound (no warranty or certification this code works all the time). I have seen different functions for different values to be returned (char strings, ints, guids, etc.) so you do not have to convert anything.
Davis 10/1/2007 10:51 AM

I don't see the URL I pasted in my comments, so here it is:
http://blogs.vandamme.com/development/2007/06/parse_comma_sep.html
sanjeev sharma 4/19/2008 10:58 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

http://sanjevsharma.blogspot.com/2008/04/set-vs-select-sql-server.html
Johnny 6/2/2008 4:20 PM

Great site, alot of code to take in.
http://www.sqlset.com
Abhishek M 6/18/2008 10:58 PM

Thanks.. That was very helpful to make choice between SET and SELECT..
John 7/8/2008 12:34 PM

Very useful, great site.
http://www.sqlset.com
JC 3/24/2009 3:37 AM

Well done!
getusama 7/1/2009 5:09 AM

Thank you so much. I was looking for exact answer to a weird problem I was facing with cursors.
10/26/2011 8:31 AM

difference is between using SET and SELECT when assigning varaibles in T-SQL? - dBforums
11/17/2013 9:07 AM

SET vs. SELECT &#8211; What&#8217;s the difference? | Ask Programming &amp; Technology
12/14/2015 1:53 AM

SET vs. SELECT &#8211; What&#039;s the difference? | Questions
11/24/2017 9:59 PM

SET versus SELECT when assigning variables? - QuestionFocus
5/22/2022 2:49 PM

SET versus SELECT when assigning variables? &#8211; w3toppers.com
9/1/2022 8:57 AM

SET versus SELECT when assigning variables?
9/16/2022 6:39 PM

SET versus SELECT when assigning variables? &#8211; Row Coding
11/10/2022 2:26 PM

[sql] ????????? ????????? ??? SET ??? SELECT? - ????????????
11/20/2022 11:40 AM

SET vs. SELECT - ??Cu??l es la diferencia? - Fallosweb.com
12/30/2022 1:26 PM

SET versus SELECT when assigning variables? - Design Corral