How to tell if SQL server is trimming the result if TOP is used?

Question

I have this SQL query:

SELECT TOP 10 * FROM MyTable

How do I tell if the results were cut short, i.e. MyTable actually has more than 10 rows?

Answer 1

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

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

This total is calculated after the FROM and WHERE clauses are executed, but before the TOP clause is applied.

Answer 2

Cheat:

SELECT TOP 11 * FROM MyTable

and check if there are 11 rows :-) Then ignore the eleventh row. Note that from a comment you wrote, perhaps you didn't comprehend: you do this query instead of the TOP 10 query.

If there are <= 10 rows, then the entire resultset would have been returned by TOP 10. If there are 11 rows, then clearly a TOP 10 wouldn't have been enough.

Answer 3

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

SELECT COUNT(*)
FROM MyTable

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