ROWCOUNT when setting variables
At work last week the question was raised about what @@ROWCOUNT value was returned when you set variables in a SELECT statement. One of the most common problems you come across reviewing code as a DBA is the setting (or not) of a variable to something that the developer wasn’t expecting!
Using the following table:
USE tempdb
GO
CREATE TABLE nums ( num int, val char(10) ) ;
INSERT INTO nums ( num, val ) VALUES ( 1 , 'one') ;
GO
The two simplest ways you can set a single variable from data in a table are:
First Method
DECLARE @num int
SET @num = ( SELECT num FROM nums ) ;
SELECT @num , @@ROWCOUNT -- 1, 1
GO
Second Method
DECLARE @num int
SELECT @num = num
FROM nums ;
SELECT @num, @@ROWCOUNT -- 1, 1
GO
The first method may be considered by some to be safer because using that syntax then if the table contains more than one row you will get an error e.g.
INSERT INTO nums ( num, val ) VALUES ( 2 , 'two') ;
GO
DECLARE @num int
SET @num = ( SELECT num FROM nums ) ;
SELECT @@ROWCOUNT
GO
Will give you the error:
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Therefore if your table contains more than one row, you will need to use a restriction so that only one row is returned e.g.
DECLARE @num int
SET @num = ( SELECT num FROM nums WHERE val = 'one') ;
SELECT @num, @@ROWCOUNT -- 1, 1
GO
or you can specify TOP 1 to limit what is returned, and if you want a specific row you will also need an ORDER BY clause.
DECLARE @num int
SET @num = ( SELECT TOP 1 num FROM nums ORDER BY num ) ;
SELECT @num, @@ROWCOUNT -- 1, 1
GO
DECLARE @num int
SET @num = ( SELECT TOP 1 num FROM nums ORDER BY num DESC ) ;
SELECT @num, @@ROWCOUNT -- 2, 1
GO
or if you are wanting to be really safe a restriction a TOP clause and an ORDER BY:
DECLARE @num int
SET @num = ( SELECT TOP 1 num FROM nums WHERE val = 'one' ORDER BY num DESC ) ;
SELECT @num, @@ROWCOUNT -- 1, 1
GO
Using the second method to set the variable will not give you an error, and this is often the cause of many of the problems e.g.
DECLARE @num int
SELECT @num = num
FROM nums ;
SELECT @num, @@ROWCOUNT -- 2, 2
GO
@@ROWCOUNT in this case returns 2 and not one as you may expect. The value returned is not always guaranteed for instance if I add a clustered descending index:
CREATE CLUSTERED INDEX CLX on nums (NUM DESC);
GO
The same query will return a different value;
DECLARE @num int
SELECT @num = num
FROM nums ;
SELECT @num, @@ROWCOUNT -- 1, 2
GO
If you are relying on this being a given value within your application, then it may suddenly start to behave differently even though the code has not changed. Like the first method you can use an order by or restriction to make sure that you are getting the row you expect:
DECLARE @num int
SELECT @num = num
FROM nums
ORDER BY num DESC;
SELECT @num, @@ROWCOUNT -- 1, 2
GO
or better:
DECLARE @num int
SELECT @num = num
FROM nums
WHERE val = 'one'
ORDER BY num DESC;
SELECT @num, @@ROWCOUNT -- 1, 1
GO
or even better:
DECLARE @num int
SELECT TOP 1 @num = num
FROM nums
WHERE val = 'one'
ORDER BY num DESC;
SELECT @num, @@ROWCOUNT -- 1, 1
GO
If you are setting more than one variable, then you can't use a single set statement and you would have to write:
DECLARE @num int, @val char(10) ;
SET @num = ( SELECT TOP 1 num FROM nums ORDER BY num DESC ) ;
SET @val = ( SELECT TOP 1 val FROM nums ORDER BY num DESC ) ;
SELECT @num, @val -- 2, two
GO
but this method is starting to get a bit messy and inefficient. You can use multiple sub-queries with a SELECT statement:
DECLARE @num int, @val char(10) ;
SELECT @num = ( SELECT TOP 1 num FROM nums ORDER BY num DESC ),
@val = ( SELECT TOP 1 val FROM nums ORDER BY num DESC ) ;
SELECT @num, @val, @@ROWCOUNT -- 2, two, 1
GO
Looking at the query plan for this:
and comparing it to when you don't use sub-queries, such as the following statement:
DECLARE @num int, @val char(10) ;
SELECT TOP 1 @num = num,
@val = val
FROM nums
ORDER BY num DESC ;
SELECT @num, @val, @@ROWCOUNT -- 2, two, 1
GO
and look at the query plan:
If there is a where clause that utilises the clustered index, you would get an index seek:
DECLARE @num int, @val char(10) ;
SELECT TOP 1 @num = num,
@val = val
FROM nums
WHERE num = 2
ORDER BY num DESC ;
SELECT @num, @val, @@ROWCOUNT -- 2, two, 1
GO
Therefore I can conclude that checking @@ROWCOUNT may have some use when setting variables, although on it's own it will not guarantee that you are returning the value you are expecting.