Systems Engineering and RDBMS

SET ROWCOUNT and table functions

Posted by decipherinfosys on April 20, 2007

SET ROWCOUNT command is used to limit the number of rows returned by query. It stops the processing of the query after fixed number of rows mentioned in the command are returned.

Row limit applies to all the select statements, DML statements, table functions and triggers executed after setting ROWCOUNT value. In this blog, we will see the behavior of rowcount when table function is used in select statement with left outer join clause. Purpose of the example is to demonstrate the behavior. Let us first create two tables and table function to retrieve data from one of the table. You can use query analyzer or management studio.

CREATE TABLE dbo.TEST_GROUP
(
TEST_GROUP_ID INT,
NAME VARCHAR(20),
CONSTRAINT PK_TEST_GROUP PRIMARY KEY(TEST_GROUP_ID)
)
GO

INSERT INTO dbo.TEST_GROUP VALUES(1,’TEST GROUP 1′);
INSERT INTO dbo.TEST_GROUP VALUES(2,’TEST GROUP 2′);
INSERT INTO dbo.TEST_GROUP VALUES(3,’TEST GROUP 3′);
INSERT INTO dbo.TEST_GROUP VALUES(4,’TEST GROUP 4′);
INSERT INTO dbo.TEST_GROUP VALUES(5,’TEST GROUP 5′);
GO

CREATE TABLE dbo.TEST_USER
(
TEST_USER_ID INT NOT NULL,
NAME VARCHAR(20),
TEST_GROUP_ID INT,
CONSTRAINT PK_TEST_USER PRIMARY KEY(TEST_USER_ID),
)
GO

INSERT INTO dbo.TEST_USER VALUES(1,’TEST USER 1′,2);
INSERT INTO dbo.TEST_USER VALUES(2,’TEST USER 2′,3);
INSERT INTO dbo.TEST_USER VALUES(3,’TEST USER 3′,1);
INSERT INTO dbo.TEST_USER VALUES(4,’TEST USER 4′,5);
INSERT INTO dbo.TEST_USER VALUES(5,’TEST USER 5′,5);
GO

CREATE FUNCTION dbo.tab_test()
RETURNS @GroupIDs table (Group_ID int, Name VARCHAR(20))
AS
BEGIN
/* Returns a table containing the ID and name of the test groups*/

INSERT INTO @GroupIDs (Group_ID, Name)
SELECT Test_Group_ID,Name
FROM dbo.Test_Group
RETURN
END
GO

Now let us check the scenario one by one. First we will execute all queries shown below, when ROWCOUNT is not set or it is set to 0. It is default behavior when running query from Query analyzer or management studio.

SELECT tu.Test_User_ID, tu.name as User_Name, tg.name as Group_Name
FROM test_user tu
LEFT OUTER JOIN Test_Group tg
ON tg.test_Group_ID = tu.Test_Group_ID
ORDER BY tu.Test_User_ID
GO

SELECT tu.Test_User_ID, tu.name as User_Name, tg.name as Group_Name
FROM test_user tu
LEFT OUTER JOIN dbo.tab_test() tg
ON tg.Group_ID = tu.Test_Group_ID
ORDER BY tu.Test_User_ID
GO

All of the above queries will produce exact same result as shown below for our current data set.

Test_User_ID User_Name Group_Name
———— ——————– ——————–
1 TEST USER 1 TEST GROUP 2
2 TEST USER 2 TEST GROUP 3
3 TEST USER 3 TEST GROUP 1
4 TEST USER 4 TEST GROUP 5
5 TEST USER 5 TEST GROUP 5

(5 row(s) affected)

Now let us set ROWCOUNT value to 2. Setting this value to 2 is an indication to optimizer to stop processing query after 2 rows are fetched.

SET ROWCOUNT 2
GO

We will run all queries again. This time we will see the difference in result.

SELECT tu.Test_User_ID, tu.name as User_Name, tg.name as Group_Name
FROM test_user tu
LEFT OUTER JOIN Test_Group tg
ON tg.test_Group_ID = tu.Test_Group_ID
ORDER BY tu.Test_User_ID
GO

Above query returns two rows with proper value for Group_Name. Following is the result.

Test_User_ID User_Name Group_Name
———— ——————– ——————–
1 TEST USER 1 TEST GROUP 2
2 TEST USER 2 TEST GROUP 3

Now execute following query and observe the results.

SELECT tu.Test_User_ID, tu.name as User_Name, tg.name as Group_Name
FROM test_user tu
LEFT OUTER JOIN dbo.tab_test() tg
ON tg.Group_ID = tu.Test_Group_ID
ORDER BY tu.Test_User_ID
GO

Test_User_ID User_Name Group_Name
———— ——————– ——————–
1 TEST USER 1 TEST GROUP 2
2 TEST USER 2 NULL

You will see that when table function is used in join condition, group_name for second row is null.

The issue is with the use of ROWCOUNT and the different ordering in the function and the outer query. Rowcount effects the internal workings of the triggers, functions and procedures. Since Rowcount is used outside of a statement that executes the actual query, the execution plan does not take into consideration the rowcount value. From BOL:

“SET ROWCOUNT causes most SELECT, INSERT, UPDATE, and DELETE statements to stop processing when they have affected the specified number of rows. This behavior applies inside the firing of triggers too.”

The way to correct or change it to behave would be:

SELECT top 2 tu.Test_User_ID, tu.name as User_Name, tg.name as Group_Name
FROM test_user tu
LEFT OUTER JOIN dbo.tab_test() tg
ON tg.Group_ID = tu.Test_Group_ID
ORDER BY tu.Test_User_ID
GO

i.e. using TOP instead of ROWCOUNT. Since TOP is part of the query itself, it gets applied on the result set and not to individual parts of the from clause.

And the other option is to use an outer apply (only valid in SQL Server 2005) operator instead of the left outer join instead if that applies to the function in your scenario, example:

The function would be changed to:

alter FUNCTION dbo.tab_test(@id_value int)
RETURNS @GroupIDs table (Group_ID int, Name VARCHAR(20))
AS
BEGIN
/* Returns a table containing the ID and name of the test groups*/
INSERT INTO @GroupIDs (Group_ID, Name)
SELECT Test_Group_ID,Name
FROM dbo.Test_Group
where test_group_id = @id_value
RETURN
END
GO

set rowcount 2
SELECT tu.Test_User_ID, tu.name as User_Name, tg.name as Group_Name
FROM test_user tu
outer apply dbo.tab_test(tu.test_group_id) tg
ORDER BY tu.Test_User_ID

That way, the execution plan gets changed and the value of the rowcount does not effect the inner workings of the function.

Another thing to note is the statement from MSFT in BOL:

Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. We recommend that DELETE, INSERT, and UPDATE statements that currently are using SET ROWCOUNT be rewritten to useTOP.

Since the TOP clause has enhanced functionality in version 2005, you can use TOP in lieu of ROWCOUNT.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: