Use of SET ROWCOUNT in SQL Server - Limiting result set

Question

I have a sql statement that consists of multiple SELECT statements. I want to limit the total number of rows coming back to let's say 1000 rows. I thought that using the SET ROWCOUNT 1000 directive would do this...but it does not. For example:

SET ROWCOUNT 1000

select orderId from TableA

select name from TableB

My initial thought was that SET ROWCOUNT would apply to the entire batch, not the individual statements within it. The behavior I'm seeing is it will limit the first select to 1000 and then the second one to 1000 for a total of 2000 rows returned. Is there any way to have the 1000 limit applied to the batch as a whole?

Answer 1

Not in one statement. You're going to have to subtract @@ROWCOUNT from the total rows you want after each statement, and use a variable (say, "@RowsLeft") to store the remaining rows you want. You can then SELECT TOP @RowsLeft from each individual query...

Answer 2

Untested and doesn't make use of ROWCOUNT, but could give you an idea?

Assumes col1 in TableA and TableB are the same type.

SELECT TOP 1000 * FROM ( select orderId from TableA UNION ALL select name from TableB ) t

Answer 3

Use TOP not ROWCOUNT

http://msdn.microsoft.com/en-us/library/ms189463.aspx

You trying to get 1000 rows MAX from all tables right?

I think other methods may fill up with from the top queries first, and you may never get results from the lower ones.

Answer 4

And how would you ever see any records from the second query if the first always returns more than 1000 if you were able to do this in a batch?

If the queries are simliar enough you could try to do this through a union and use the rowcount on that as it would only be one query at that point. If the queries are differnt in the columns returned I'm not sure what you would get by limiting the entire group to 1000 rows because the meanings would be different. From a user perspective I'd rather consistently get 500 orders and 500 customer names than 998 orers and 2 names one day and 210 orders and 790 names the next. It would be impossible to use the application especially if you happened to be most interested in the information in the second query.

Answer 5

The requirement sounds odd. Unless you are unioning or joining the data from the two selects, to consider them as one so that you apply a max rows simply does not make sense, since they are unrelated queries at that point. If you really need to do this, try:

select top 1000 from (
    select orderId, null as name, 'TableA' as Source from TableA
    union all
    select null as orderID, name, 'TableB' as Source from TableB
) a order by Source

Answer 6

The following worked for me:

CREATE PROCEDURE selectTopN
(
    @numberOfRecords int
)
AS
    SELECT TOP (@numberOfRecords) * FROM Customers
GO

Answer 7

SET ROWCOUNT applies to each individual query. In your given example, it's applied twice, once to each SELECT statement, since each statement is its own batch (they're not grouped or unioned or anything, and so execute completely separately).

@RedFilter's approach seems the most likely to give you what you want.