How to Include the Total Number of Returned Rows in the Resultset from Select T-SQL Command

How to get total number of rows in a executed select statement?

You either have to use SELECT COUNT(*) ... with the same condition or add a column with the row-count via ROW_NUMBER function:

SELECT a.Emp,b.orders, RN = ROW_NUMBER () OVER (ORDER BY a.Emp,b.orders) 
FROM table as a inner join table1 b on a.ID=B.ID

...or use @@ROWCOUNT after the select.

Instead of ROW_NUMBER it's easier to use COUNT(*) OVER ( Order By ...) where each row contains the same total-count whereas ROW_NUMBER would return a sequential number where only the last record(acc. to the ORDER BY) would have the total-count.

So what Aaron has already meantioned in his answer.

Add a row number to result set of a SQL query

The typical pattern would be as follows, but you need to actually define how the ordering should be applied (since a table is, by definition, an unordered bag of rows). One way to do that if you don't care about a specific order otherwise is to use the leading key(s) of a covering index, the leading key(s) of the clustered index, or the columns in any group by / order by clauses. In this case I'll assume A is the single-column clustering key for t:

SELECT t.A, t.B, t.C, number = ROW_NUMBER() OVER (ORDER BY t.A)
  FROM dbo.tableZ AS t
  ORDER BY t.A;

If you truly don't care about order, you can generate arbitrary/nondeterministic row numbering using:

ROW_NUMBER() OVER (ORDER BY @@SPID)

-- or for serial plans

ROW_NUMBER() OVER (ORDER BY @@TRANCOUNT)

Little tricks I picked up from Paul White in this article (see "Paul's Solution").

Not sure what the variables in your question are supposed to represent (they don't match).

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)?

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 :).

Send the total rows/last row, included with the resultset

COUNT(*) with an empty OVER() clause will give you the total row count. You could then add that into the WHERE clause if you need the last row returned.

SELECT *
FROM
(
    SELECT *, 
    (ROW_NUMBER() OVER (ORDER BY itemid)) AS row,
    COUNT(*) OVER() AS row_count
    /* the rest of a big complex query that, so far, works.*/
) AS q
 WHERE
    (           
        row BETWEEN @start AND @end or row=row_count
    )

Need a row count after SELECT statement: what's the optimal SQL approach?

There are only two ways to be 100% certain that the COUNT(*) and the actual query will give consistent results:

• Combined the COUNT(*) with the query, as in your Approach 2. I recommend the form you show in your example, not the correlated subquery form shown in the comment from kogus.
• Use two queries, as in your Approach 1, after starting a transaction in SNAPSHOT or SERIALIZABLE isolation level.

Using one of those isolation levels is important because any other isolation level allows new rows created by other clients to become visible in your current transaction. Read the MSDN documentation on SET TRANSACTION ISOLATION for more details.

How to get row count using ResultSet in Java?

If you have access to the prepared statement that results in this resultset, you can use

connection.prepareStatement(sql, 
  ResultSet.TYPE_SCROLL_INSENSITIVE, 
  ResultSet.CONCUR_READ_ONLY);

This prepares your statement in a way that you can rewind the cursor. This is also documented in the ResultSet Javadoc

In general, however, forwarding and rewinding cursors may be quite inefficient for large result sets. Another option in SQL Server would be to calculate the total number of rows directly in your SQL statement:

SELECT my_table.*, count(*) over () total_rows
FROM my_table
WHERE ...

---

Related Topics