It seems like a simple question but the answer is difficult as I see it and since I have been unable to find a usable solution to my challenge.
What I would like to do is to have a TSQL Select return the number of records in the Result as if TOP (n) had not been used. Example:
I have a table called Orders containing more than 1.000 records with OrderDate = '2015/07/21' and my client application has a threshold for returning records at 100 and therefore the TSQL would look like
SELECT TOP (100) * FROM Orders Where OrderDate = '2015/07/21' ORDER by OrderTime Desc
Now I would like to "tell" the client that only 100 of 1.000 records are shown in the client application grid. Is there a way to return a value indicating that if TOP (100) had not been used the resultset would have been 1.000. I know I could create the same TSQL using COUNT() (SELECT COUNT(*) FROM Orders Where OrderDate = '2015/07/21' ORDER by OrderTime Desc) and return that in a variable in the SELECT statement or even creating the COUNT() as a subquery and return it as a column, but I would like to avoid running multiple TSQL's.
Since SQL Server already needs to select the entire recordset and sort it (ORDER BY) and return only the first 100 the total number of records in the initial snapshot must somehow be available.
Do you guys have any idea as to how to solve this? Looking forward to hearing suggestions.