How to determine if SQL server completes the result if TOP is used?

Question

I have this SQL query:

SELECT TOP 10 * FROM MyTable 

How do I know if results are interrupted, for example MyTable has more than 10 rows?

Answer 1

The easiest way is to add a COUNT..OVER column that returns full rows in the table in the same query:

 SELECT TOP 10 *, COUNT(*) OVER() As TotalRows FROM MyTable 

This amount is calculated after executing the FROM and WHERE clauses, but before applying the TOP clause.

Answer 2

Cheat:

 SELECT TOP 11 * FROM MyTable 

and check if there are 11 lines :-) Then ignore the eleventh line. Please note that from the comment you wrote, you might not understand: you are making this request instead of the TOP 10 request.

If there are <= 10 rows, then the entire result set would be returned TOP 10 . If there are 11 lines, then it is clear that TOP 10 would not be enough.

Answer 3

You can use COUNT to get the total number of rows.

 SELECT COUNT(*) FROM MyTable 

You can check if this result is greater than 10 to determine if it has more than 10 rows.

Answer 4

You can return the total number of rows using count(*) as an extra column.

Something like that:

 select top 10 T1.*, T2.Total from MyTable as T1 outer apply (select count(*) as Total from MyTable) as T2