Monday, August 02, 2004
Difference Between SET and SELECT
T-SQL Programmers use both SET and SELECT Statements for variable assignments. Lets see what are the differences.
1. SET is the ANSI Standard for variable assignment. Variable assignment using SELECT is T-SQL Proprietory.
2. SELECT can be used for multiple variable assignment. Using SET you can only assign one variable at a time.
DECLARE @myval1 int
DECLARE @myval2 int
SET @myVal1 = 5
SET @myVal2 = 6
SELECT @myVal1 = 5, @myVal2 = 6
3. Different Behavior when No rows are Returned.
If variables are assigned in the SELECT then the value of the variables will remain unchanged, but thats not the case with SET. See the following example.
DECLARE @id INTEGER
SET @id = 0
SELECT @id = id FROM sysobjects WHERE name = 'does not exist'
SELECT @id -- Returns Zero
SET @id= (SELECT id FROM sysobjects WHERE name = 'does not exist')
SELECT @id -- Returns NULL
But if the SELECT includes an aggregate function it will NOT retain the initial value.
DECLARE @id INTEGER
SET @id = 0
SELECT @id = Max(id) FROM sysobjects WHERE name = 'does not exist'
SELECT @id -- Returns NULL
4. Different Behavior when Multiple rows are returned by the expression in the right side.
When using SET, it returns the 'Subquery Returned more than one value' Error.
DECLARE @id INTEGER
SET @id = 0
SET @id= (SELECT id FROM sysobjects)
-- Returns Error
But with SELECT it assigns the 'last' returned value to the variable, whic can be anything, if an ORDER BY clause is not specified, as in -
DECLARE @id INTEGER
SET @id = 0
SELECT @id = id FROM sysobjects
SELECT @id
So you have to be careful, to avoid disatsters. Lets take an example.
Suppose you want to assign the orderid of the Last order placed by a given customer, to a variable.
(There might be better way to do that with an aggregate function, but this is just an example)
USE Northwind
GO
DECLARE @OrderId int
SELECT @OrderId = OrderId
FROM Orders
WHERE CustomerId = 'VINET'
ORDER BY OrderId ASC
SELECT @OrderId
Here we have used ORDER BY OrderId ASC. So the last OrderID of the customer will be assigned to the variable @OrderId. But suppose you add TOP 1 to the above query, thinking that you only want one OrderId anyway, then you will be getting only the first order of the customer.
USE Northwind
GO
DECLARE @OrderId int
SELECT TOP 1 @OrderId = OrderId
FROM Orders
WHERE CustomerId = 'VINET'
ORDER BY OrderId ASC
SELECT @OrderId
To get the expected result you have to change the ORDER BY clause to DESC.
USE Northwind
GO
DECLARE @OrderId int
SELECT TOP 1 @OrderId = OrderId
FROM Orders
WHERE CustomerId = 'VINET'
ORDER BY OrderId DESC
SELECT @OrderId
5. Looping Behavior of SELECT
Further exploring the above point, we can introduce some intuitive looping behaviour using SELECT. Consider the following example.
CREATE TABLE #tmp (a int)
INSERT INTO #tmp VALUES(1)
INSERT INTO #tmp VALUES(2)
INSERT INTO #tmp VALUES(3)
INSERT INTO #tmp VALUES(4)
INSERT INTO #tmp VALUES(5)
DECLARE @p int
SELECT @p = isnull(@p, 1) * a FROM #tmp
SELECT @p
Here @p become 120, the product of all values of a. We can observe that the assignment is carried out once for each row of in the table #tmp.
The same logic is used in the varchar concatenation technique shown below.
USE PUBS
GO
DECLARE @vchAuthors VARCHAR(400)
SET @vchAuthors = ''
SELECT @vchAuthors = @vchAuthors +
CASE WHEN @vchAuthors = '' THEN '' ELSE ', ' END + au_lname
FROM authors
SELECT @vchAuthors
Thats all I have about it. SQL Server MVP Narayana Vyas has an article abt the differences between SET and SELECT, which also includes a performance comparison between the two.
1. SET is the ANSI Standard for variable assignment. Variable assignment using SELECT is T-SQL Proprietory.
2. SELECT can be used for multiple variable assignment. Using SET you can only assign one variable at a time.
DECLARE @myval1 int
DECLARE @myval2 int
SET @myVal1 = 5
SET @myVal2 = 6
SELECT @myVal1 = 5, @myVal2 = 6
3. Different Behavior when No rows are Returned.
If variables are assigned in the SELECT then the value of the variables will remain unchanged, but thats not the case with SET. See the following example.
DECLARE @id INTEGER
SET @id = 0
SELECT @id = id FROM sysobjects WHERE name = 'does not exist'
SELECT @id -- Returns Zero
SET @id= (SELECT id FROM sysobjects WHERE name = 'does not exist')
SELECT @id -- Returns NULL
But if the SELECT includes an aggregate function it will NOT retain the initial value.
DECLARE @id INTEGER
SET @id = 0
SELECT @id = Max(id) FROM sysobjects WHERE name = 'does not exist'
SELECT @id -- Returns NULL
4. Different Behavior when Multiple rows are returned by the expression in the right side.
When using SET, it returns the 'Subquery Returned more than one value' Error.
DECLARE @id INTEGER
SET @id = 0
SET @id= (SELECT id FROM sysobjects)
-- Returns Error
But with SELECT it assigns the 'last' returned value to the variable, whic can be anything, if an ORDER BY clause is not specified, as in -
DECLARE @id INTEGER
SET @id = 0
SELECT @id = id FROM sysobjects
SELECT @id
So you have to be careful, to avoid disatsters. Lets take an example.
Suppose you want to assign the orderid of the Last order placed by a given customer, to a variable.
(There might be better way to do that with an aggregate function, but this is just an example)
USE Northwind
GO
DECLARE @OrderId int
SELECT @OrderId = OrderId
FROM Orders
WHERE CustomerId = 'VINET'
ORDER BY OrderId ASC
SELECT @OrderId
Here we have used ORDER BY OrderId ASC. So the last OrderID of the customer will be assigned to the variable @OrderId. But suppose you add TOP 1 to the above query, thinking that you only want one OrderId anyway, then you will be getting only the first order of the customer.
USE Northwind
GO
DECLARE @OrderId int
SELECT TOP 1 @OrderId = OrderId
FROM Orders
WHERE CustomerId = 'VINET'
ORDER BY OrderId ASC
SELECT @OrderId
To get the expected result you have to change the ORDER BY clause to DESC.
USE Northwind
GO
DECLARE @OrderId int
SELECT TOP 1 @OrderId = OrderId
FROM Orders
WHERE CustomerId = 'VINET'
ORDER BY OrderId DESC
SELECT @OrderId
5. Looping Behavior of SELECT
Further exploring the above point, we can introduce some intuitive looping behaviour using SELECT. Consider the following example.
CREATE TABLE #tmp (a int)
INSERT INTO #tmp VALUES(1)
INSERT INTO #tmp VALUES(2)
INSERT INTO #tmp VALUES(3)
INSERT INTO #tmp VALUES(4)
INSERT INTO #tmp VALUES(5)
DECLARE @p int
SELECT @p = isnull(@p, 1) * a FROM #tmp
SELECT @p
Here @p become 120, the product of all values of a. We can observe that the assignment is carried out once for each row of in the table #tmp.
The same logic is used in the varchar concatenation technique shown below.
USE PUBS
GO
DECLARE @vchAuthors VARCHAR(400)
SET @vchAuthors = ''
SELECT @vchAuthors = @vchAuthors +
CASE WHEN @vchAuthors = '' THEN '' ELSE ', ' END + au_lname
FROM authors
SELECT @vchAuthors
Thats all I have about it. SQL Server MVP Narayana Vyas has an article abt the differences between SET and SELECT, which also includes a performance comparison between the two.
Comments:
<< Home
In the "Different Behavior when No rows are Returned." section you wrote: "But if the SELECT includes an aggregate function it will NOT retain the initial value."
This should be "if the SELECT includes an aggregate function AND it DOES NOT include an GROUP BY clause". That's because the following:
DECLARE @id INTEGER
SET @id = 0
SELECT @id = Max(id) FROM sysobjects WHERE name = 'does not exist' GROUP BY xtype
SELECT @id
... returns 0, not NULL
Razvan
This should be "if the SELECT includes an aggregate function AND it DOES NOT include an GROUP BY clause". That's because the following:
DECLARE @id INTEGER
SET @id = 0
SELECT @id = Max(id) FROM sysobjects WHERE name = 'does not exist' GROUP BY xtype
SELECT @id
... returns 0, not NULL
Razvan
# posted by 
: 11:32 AM
: 11:32 AM
Tony Rogerson gives a good and thorough discussion on this topic too here: http://sqlserverfaq.com/blogs/blogs/tonyrogerson/archive/2006/05/18/449.aspx
# posted by : 1:49 PM
: 1:49 PM
inventory journal
operation software
director software
driver software
factory software
pub software
baker software
baker software
county software
dancing log
Post a Comment
operation software
director software
driver software
factory software
pub software
baker software
baker software
county software
dancing log
<< Home
