TSQL: Is there a way to limit the rows returned and count the total that would have been returned without the limit (without adding it to every row)?

Question

I'm working to update a stored procedure that current selects up to n rows, if the rows returned = n, does a select count without the limit, and then returns the original select and the total impacted rows.

Kinda like:

SELECT TOP (@rowsToReturn)
    A.data1,
    A.data2
FROM
    mytable A

SET @maxRows = @@ROWCOUNT
IF @rowsToReturn = @@ROWCOUNT
BEGIN
  SET @maxRows = (SELECT COUNT(1) FROM mytableA)
END    

I'm wanting reduce this to a single select statement. Based on this question, COUNT(*) OVER() allows this, but it is put on every single row instead of in an output parameter. Maybe something like FOUND_ROWS() in MYSQL, such as a @@TOTALROWCOUNT or such.

As a side note, since the actual select has an order by, the data base will need to already traverse the entire set (to make sure that it gets the correct first n ordered records), so the database should already have this count somewhere.

Answer 1

As @MartinSmith mentioned in a comment on this question, there is no direct (i.e. pure T-SQL) way of getting the total numbers of rows that would be returned while at the same time limiting it. In the past I have done the method of:

• dump the query to a temp table to grab @@ROWCOUNT (the total set)
• use ROW_NUBMER() AS [ResultID] on the ordered results of the main query
• SELECT TOP (n) FROM #Temp ORDER BY [ResultID] or something similar

Of course, the downside here is that you have the disk I/O cost of getting those records into the temp table. Put [tempdb] on SSD? :)

I have also experienced the "run COUNT(*) with the same rest of the query first, then run the regular SELECT" method (as advocated by @Blam), and it is not a "free" re-run of the query:

• It is a full re-run in many cases. The issue is that when doing COUNT(*) (hence not returning any fields), the optimizer only needs to worry about indexes in terms of the JOIN, WHERE, GROUP BY, ORDER BY clauses. But when you want some actual data back, that could change the execution plan quite a bit, especially if the indexes used to get the COUNT(*) are not "covering" for the fields in the SELECT list.
• The other issue is that even if the indexes are all the same and hence all of the data pages are still in cache, that just saves you from the physical reads. But you still have the logical reads.

I'm not saying this method doesn't work, but I think the method in the Question that only does the COUNT(*) conditionally is far less stressful on the system.

The method advocated by @Gordon is actually functionally very similar to the temp table method I described above: it dumps the full result set to [tempdb] (the INSERTED table is in [tempdb]) to get the full @@ROWCOUNT and then it gets a subset. On the downside, the INSTEAD OF TRIGGER method is:

• a lot more work to set up (as in 10x - 20x more): you need a real table to represent each distinct result set, you need a trigger, the trigger needs to either be built dynamically, or get the number of rows to return from some config table, or I suppose it could get it from CONTEXT_INFO() or a temp table. Still, the whole process is quite a few steps and convoluted.

• very inefficient: first it does the same amount of work dumping the full result set to a table (i.e. into the INSERTED table--which lives in [tempdb]) but then it does an additional step of selecting the desired subset of records (not really a problem as this should still be in the buffer pool) to go back into the real table. What's worse is that second step is actually double I/O as the operation is also represented in the transaction log for the database where that real table exists. But wait, there's more: what about the next run of the query? You need to clear out this real table. Whether via DELETE or TRUNCATE TABLE, it is another operation that shows up (the amount of representation based on which of those two operations is used) in the transaction log, plus is additional time spent on the additional operation. AND, let's not forget about the step that selects the subset out of INSERTED into the real table: it doesn't have the opportunity to use an index since you can't index the INSERTED and DELETED tables. Not that you always would want to add an index to the temp table, but sometimes it helps (depending on the situation) and you at least have that choice.

• overly complicated: what happens when two processes need to run the query at the same time? If they are sharing the same real table to dump into and then select out of for the final output, then there needs to be another column added to distinguish between the SPIDs. It could be @@SPID. Or it could be a GUID created before the initial INSERT into the real table is called (so that it can be passed to the INSTEAD OF trigger via CONTEXT_INFO() or a temp table). Whatever the value is, it would then be used to do the DELETE operation once the final output has been selected. And if not obvious, this part influences a performance issue brought up in the prior bullet: TRUNCATE TABLE cannot be used as it clears the entire table, leaving DELETE FROM dbo.RealTable WHERE ProcessID = @WhateverID; as the only option.

  Now, to be fair, it is possible to do the final SELECT from within the trigger itself. This would reduce some of the inefficiency as the data never makes it into the real table and then also never needs to be deleted. It also reduces the over-complication as there should be no need to separate the data by SPID. However, this is a very time-limited solution as the ability to return results from within a trigger is going bye-bye in the next release of SQL Server, so sayeth the MSDN page for the disallow results from triggers Server Configuration Option:

  This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you set this value to 1.

The only actual way to do:

• the query one time
• get a subset of rows
• and still get the total row count of the full result set

is to use .Net. If the procs are being called from app code, please see "EDIT 2" at the bottom. If you want to be able to randomly run various stored procedures via ad hoc queries, then it would have to be a SQLCLR stored procedure so that it could be generic and work for any query as stored procedures can return dynamic result sets and functions cannot. The proc would need at least 3 parameters:

• @QueryToExec NVARCHAR(MAX)
• @RowsToReturn INT
• @TotalRows INT OUTPUT

The idea is to use "Context Connection = true;" to make use of the internal / in-process connection. You then do these basic steps:

1. call ExecuteDataReader()
2. before you read any rows, do a GetSchemaTable()
3. from the SchemaTable you get the result set field names and datatypes
4. from the result set structure you construct a SqlDataRecord
5. with that SqlDataRecord you call SqlContext.Pipe.SendResultsStart(_DataRecord)
6. now you start calling Reader.Read()
7. for each row you call:
   1. Reader.GetValues()
   2. DataRecord.SetValues()
   3. SqlContext.Pipe.SendResultRow(_DataRecord)
   4. RowCounter++
8. Rather than doing the typical "while (Reader.Read())", you instead include the @RowsToReturn param: while(Reader.Read() && RowCounter < RowsToReturn.Value)
9. After that while loop, call SqlContext.Pipe.SendResultsEnd() to close the result set (the one that you are sending, not the one you are reading)
10. then do a second while loop that cycles through the rest of the result, but never gets any of the fields: while (Reader.Read()) { RowCounter++; }
11. then just set TotalRows = RowCounter; which will pass back the number of rows for the full result set, even though you only returned the top n rows of it :)

Not sure how this performs against the temp table method, the dual call method, or even @M.Ali's method (which I have also tried and kinda like, but the question was specific to not sending the value as a column), but it should be fine and does accomplish the task as requested.

EDIT:
Even better! Another option (a variation on the above C# suggestion) is to use the @@ROWCOUNT from the T-SQL stored procedure, sent as an OUTPUT parameter, rather than cycling through the rest of the rows in the SqlDataReader. So the stored procedure would be similar to:

CREATE PROCEDURE SchemaName.ProcName
(
   @Param1 INT,
   @Param2 VARCHAR(05),
   @RowCount INT OUTPUT = -1 -- default so it doesn't have to be passed in
)
AS
SET NOCOUNT ON;

{any ol' query}

SET @RowCount = @@ROWCOUNT;

Then, in the app code, create a new SqlParameter, Direction = Output, for "@RowCount". The numbered steps above stay the same, except the last two (10 and 11), which change to:

10. Instead of the 2nd while loop, just call Reader.Close()
11. Instead of using the RowCounter variable, set TotalRows = (int)RowCountOutputParam.Value;

I have tried this and it does work. But so far I have not had time to test the performance against the other methods.

EDIT 2:
If the T-SQL stored procs are being called from the app layer (i.e. no need for ad hoc execution) then this is actually a much simpler variation of the above C# methods. In this case you don't need to worry about the SqlDataRecord or the SqlContext.Pipe methods. Assuming you already have a SqlDataReader set up to pull back the results, you just need to:

1. Make sure the T-SQL stored proc has a @RowCount INT OUTPUT = -1 parameter
2. Make sure to SET @RowCount = @@ROWCOUNT; immediately after the query
3. Register the OUTPUT param as a SqlParameter having Direction = Output
4. Use a loop similar to: while(Reader.Read() && RowCounter < RowsToReturn) so that you can stop retrieving results once you have pulled back the desired amount.
5. Remember to not limit the result in the stored proc (i.e. no TOP (n))

At that point, just like what was mentioned in the first "EDIT" above, just close the SqlDataReader and grab the .Value of the OUTPUT param :).

Answer 2

How about this....

DECLARE @N INT = 10

;WITH CTE AS
 (
  SELECT 
    A.data1,
    A.data2
  FROM  mytable A 
 )
SELECT TOP (@N) * , (SELECT COUNT(*) FROM CTE) Total_Rows
FROM CTE 

The last column will be populated with the total number of rows it would have returned without the TOP Clause.

The issue with your requirement is, you are expecting a SINGLE select statement to return a table and also a scalar value. which is not possible.

A Single select statement will return a table or a scalar value. OR you can have two separate selects one returning a Scalar value and other returning a scalar. Choice is yours :)