Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SET ROWCOUNT and TVFs

By Peter He,

In SQL server 2000, when a stored procedure needs to return a number of records based on client’s request, we usually use SET ROWCOUNT @RequestedRecordNumber.  Though TOP operator can also limit the rows to be returned, we have to use dynamic TSQL because it does not support expressions. SET ROWCOUNT 0 will return all qualified rows, e.g.:

USE northwind
GO

DECLARE @RequestedRecordNumber int
SET @RequestedRecordNumber=2

SET ROWCOUNT @RequestedRecordNumber

SELECT * 
 FROM dbo.[Order Details] 
 WHERE OrderID=10248 
 ORDER BY ProductID

SET ROWCOUNT 0
GO

However, when SET ROWCOUNT is applied on a TVF, it may give unexpected results. Let’s first define a table and populate some test data.

USE tempdb
GO

IF NOT EXISTS(SELECT 1 FROM sysobjects WHERE ID=OBJECT_ID('dbo.t_Emps'))
 BEGIN
     CREATE TABLE dbo.t_Emps (EmpID int NOT NULL, EmpName nvarchar(50) NOT NULL, ManagerID int NULL
       CONSTRAINT PK_t_Emps PRIMARY KEY CLUSTERED (EmpID))
 END
GO
SET NOCOUNT ON
GO
INSERT dbo.t_Emps (EmpID,EmpName,ManagerID) VALUES (1,'E1', NULL)
INSERT dbo.t_Emps (EmpID,EmpName,ManagerID) VALUES (2,'E2', 1)
INSERT dbo.t_Emps (EmpID,EmpName,ManagerID) VALUES (3,'E3', 2)
INSERT dbo.t_Emps (EmpID,EmpName,ManagerID) VALUES (4,'E4', 3)
INSERT dbo.t_Emps (EmpID,EmpName,ManagerID) VALUES (5,'E5', 4)
INSERT dbo.t_Emps (EmpID,EmpName,ManagerID) VALUES (6,'E6', 4)
INSERT dbo.t_Emps (EmpID,EmpName,ManagerID) VALUES (7,'E7', 4)
GO

The following TVF is to return all directly managed employees by a manager.

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID('dbo.fn_GetChildren'))
DROP FUNCTION dbo.fn_GetChildren
GO
CREATE FUNCTION dbo.fn_GetChildren (@EmpID int)
RETURNS @Children TABLE (EmpID int NOT NULL)
AS
BEGIN
     INSERT @Children (EmpID) 
     SELECT EmpID FROM dbo.t_Emps WHERE @EmpID IS NOT NULL AND
ManagerID=@EmpID OR @EmpID IS NULL
     RETURN
END
GO

The following query returns all the employee managed by EmpID=4:

SELECT * FROM dbo.fn_GetChildren(4) A
As expected, it returns 5,6,and 7.

Query 1: use TOP to query first two employees managed by EmpID=4  

SET ROWCOUNT 0

SELECT top 2 * FROM dbo.fn_GetChildren(4) ORDER BY EmpID DESC
GO

It returns 2 rows with EmpID=7 and 6.

Query 2: use SET ROWCOUNT to query first two employees managed by EmpID=4

SET ROWCOUNT 2
SELECT * FROM dbo. fn_GetChildren(4) ORDER BY EmpID DESC
SET ROWCOUNT 0
GO

It returns 2 rows with EmpID=6 and 5.

Check the execution plan, the plan for query 1 shows that all 3 rows are returned from the TVF. However the TVF in query 2 only returns 2 rows.

Execution pan for query 1

Execution pan for query 2

We can see that though SET ROWCOUNT is not allowed in a UDF, the UDF can inherit the SET ROWCOUNT setting from its caller and affect the result of SELECT statements in the UDF. This causes query 2 returning unexpected results. To verify the conclusion, let’s create another TVF, which gets all the managers for an employee.

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID=OBJECT_ID('dbo.fn_GetAllManagers'))

DROP FUNCTION dbo.fn_GetAllManagers
GO
CREATE FUNCTION dbo.fn_GetAllManagers (@EmpID int)
RETURNS @Ancestors TABLE (EmpID int NOT NULL)
AS
BEGIN
     IF @EmpID IS NULL
     BEGIN
           RETURN
     END 
     DECLARE @ManagerID int
     SELECT @ManagerID=ManagerID FROM dbo.t_Emps WHERE EmpID=@EmpID
     WHILE @ManagerID IS NOT NULL
     BEGIN
           INSERT @Ancestors (EmpID) VALUES (@ManagerID)
           SELECT @ManagerID=ManagerID FROM dbo.t_Emps WHERE EmpID=@ManagerID
     END
     RETURN
END

GO

Query 3: verifications

SET ROWCOUNT 0

SELECT * FROM  dbo.fn_GetAllManagers (5) B
-- select with TOP
SELECT TOP 2 * FROM  dbo.fn_GetAllManagers (5) B ORDER BY EmpID DESC
SELECT TOP 2 * FROM  dbo.fn_GetAllManagers (5) B ORDER BY EmpID ASC

SET ROWCOUNT 2
-- select with SET ROWCOUNT
SELECT * FROM  dbo.fn_GetAllManagers (5) B ORDER BY EmpID DESC
SELECT * FROM  dbo.fn_GetAllManagers (5) B ORDER BY EmpID ASC
SET ROWCOUNT 0
GO

This time both the SELECT statements using TOP and SET ROWCOUNT 2 return the same results. The execution plan shows that the TVFs in the two SELECT statements all return 4 rows. This is because in the TVF fn_GetAllManagers, all the SELECT statements return ONE row each time, which is under the limit of SET ROWCOUNT 2. If we change the TVF as follows:

ALTER FUNCTION dbo.fn_GetAllManagers (@EmpID int)
RETURNS @Ancestors TABLE (EmpID int NOT NULL)
AS

BEGIN
      IF @EmpID IS NULL
      BEGIN
            RETURN
      END

      DECLARE @ManagerID int
      DECLARE @Ancestors1 TABLE (LevelID int identity not null, EmpID int NOT NULL)

      SELECT @ManagerID=ManagerID FROM dbo.t_Emps WHERE EmpID=@EmpID

      WHILE @ManagerID IS NOT NULL
      BEGIN
            INSERT @Ancestors1 (EmpID) VALUES (@ManagerID)
            SELECT @ManagerID=ManagerID FROM dbo.t_Emps WHERE EmpID=@ManagerID
      END
      INSERT @Ancestors (EmpID) SELECT EmpID FROM @Ancestors1 ORDER BY LevelID DESC
      RETURN
END
GO

Re-run the above queries, the results of the SELECT statements with TOP and SET ROWCOUNT will not match again.

Query 4: use sub-query

When SET ROWCOUNT ON is used on a SELECT statement with sub-queries, correct results are always guaranteed regardless of the requested sorting order. This is because SQL server optimizer handles ordering when generating the execution plan. The SELECT statement is terminated when the specified rows have been selected from a set of values that has been sorted according to specified ORDER BY classification. E.g.:

SET ROWCOUNT 2

SELECT B.*
 FROM (SELECT EmpID FROM dbo.t_Emps WHERE ManagerID=4) A
    INNER JOIN dbo.t_Emps B 
	    ON A.EmpID=B.EMpID
 ORDER BY B.EmpID DESC

SELECT B.*
 FROM (SELECT EmpID FROM dbo.t_Emps WHERE ManagerID=4) A 
     INNER JOIN dbo.t_Emps B 
	     ON A.EmpID=B.EMpID 
 ORDER BY B.EmpID ASC
SET ROWCOUNT 0
GO

In the SELECT with descending order, SQL server scans the clustered index (column EmpID) in reverse order (ORDERED FORWARD) and seeks the clustered index in the same table for the same EmpID with ManagerID=4. After 2 rows are scanned (EmpID 7 and 6), 2 rows are found by the “clustered index seek” operation and the SELECT statement terminates. The “Row count” of the two operations in the execution plan is both 2.

Execution plan of Query 4 (ORDER BY EmpID DESC)

In the SELECT with ascending order, SQL server scans the clustered index (column EmpID) in ascending order (ORDERED FORWARD) and seeks the clustered index in the same table for the same EmpID with ManagerID=4. For the first 4 (EmpID=1,2,3, and 4) rows scanned, it cannot find any records when seeking the table with condition ManagerID=4. After 2 more rows are scanned (EmpID 5 and 6), 2 rows are found by the “clustered index seek” and the SELECT statements terminates. So the “Row count” for the “clustered index scan” operation is 6 and it’s 2 for the “clustered index seek” operation.

Execution plan of Query 4 (ORDER BY EmpID ASC)

Conclusion

  • Though SET ROWCOUNT n cannot be used in a UDF, the current ROWCOUNT limit setting of its caller will be applied to the SELECT statements in the UDF. This is not mentioned by SQL server BOL. The behavior may lead to unexpected results when querying data from TVFs with ORDER BY clause.
  • When SET ROWCOUNT n applies to a SELECT statement with sub-queries, the results and ordering are always guaranteed.
  • To avoid confusion and unexpected logical errors, it’s better to turn SET ROWCOUNT n on just before the final SELECT statement that returns the records. If TVFs are used in the final SELECT statement, call the TVFs and save the results in temporary tables before turning the limit on. Use the temporary tables in the final query statement instead.
  • In SQL server 2005, SET ROWCOUNT n has the same behavior as SQL server 2000.
  • In SQL server 2005, TOP operator supports expressions. It’s recommended to use TOP (n) instead of SET ROWCOUNT n.
Total article views: 9995 | Views in the last 30 days: 2
 
Related Articles
FORUM

select [Employee_ID] = EmpID from dbo.Employee GROUP BY [Employee_ID]

select [Employee_ID] = EmpID from dbo.Employee group by [Employee_ID] --Group by error

FORUM

@@Rowcount is not reliable?

@@rowcount

FORUM

Difference in rowcount with select and Table properties?

Difference in rowcount with select and Table properties?

FORUM

Scope of @@rowcount

Scope of @@rowcount

Tags
sql puzzles    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones