The problem of SET ROWCOUNT

In the previous entry, I said SET ROWCOUNT has its own prblem. Let’s talk about this now.
 
If you query data from udf, the problem is the row count limit is applied to the the udf first. Since the udf does not return all the records, the final order by clause will be incorrect.
 
e.g.:
 
USE northwind
GO
SELECT * FROM dbo.[Order Details] WHERE OrderID=10248
— three rows returned with ProductID = 11,42,72.
GO
 
Now lets define a UDF:
CREATE FUNCTION dbo.FN_Get_OrderDetails ( @OrderID int )
RETURNS @OrderDetails TABLE (ProductID int not null, Quantity smallint not null)
AS
BEGIN
 INSERT @OrderDetails (ProductID,Quantity)
 SELECT ProductID,Quantity
 FROM dbo.[Order Details]
 WHERE OrderID=@OrderID
 RETURN
END
GO
 
SET ROWCOUNT 0
SELECT top 2 * FROM dbo.FN_Get_OrderDetails (10248) ORDER BY ProductID DESC
— you get two records for product 72 and 42, correct
GO
SET ROWCOUNT 2
SELECT * FROM dbo.FN_Get_OrderDetails (10248) ORDER BY ProductID DESC
— you get two records for product 42 and 11, WRONG
GO
— check the following two queries:
SET ROWCOUNT 2
SELECT * FROM dbo.[Order Details] WHERE OrderID=10248 ORDER BY ProductID DESC
GO
SET ROWCOUNT 2
SELECT * FROM (SELECT * FROM dbo.[Order Details] WHERE OrderID=10248) A ORDER BY ProductID DESC
GO
 
So when you use SET ROWCOUNT, be carefull to query data from a udf, or tables join with udf. You may miss some data or get unexpected result.
 
— DROP FUNCTION dbo.FN_Get_OrderDetails
 
 
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

One Response to The problem of SET ROWCOUNT

  1. Youjin says:

    I will be carefull!! ^_^

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s