Sql Server TOP - used?

Question

Is there a keyword or metainformation in SQL Server that'll tell you if TOP took effect?

EX:
Select TOP 5 * From Stuff

RESULT: 5 rows

What is the best way to determine if there would have been 6 or more?

I could do:
SELECT TOP 6 count(*) FROM Stuff

But I am concerned about a separate call to retrieve the count because there actual query is much more complicated than this one and on a large table.

Thanks!

Answer 1

There is nothing automatic you can use. You could use something along these lines

DECLARE @N INT = 5;

WITH T
     AS (SELECT TOP (@N + 1) *
         FROM   master..spt_values
         ORDER  BY number)
SELECT TOP (@N) *,
             CASE
               WHEN Count(*) OVER () = (@N + 1) THEN 1
               ELSE 0
             END AS MoreRecords
FROM   T
ORDER  BY number 

Answer 2

Well, you could select the top N+1 (where N in your example is 5, so in your example select the top 6) and discard the last one in your client code, and use the presence of a sixth element to determine if TOP would have had an effect had you used N in the first place. I am not sure there is much value of doing this, however.