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!

Misbehavior

"Took effect" in what way? Meaning that it actually limited your result set? Just count what you got back. – Waterloo

@jonnyGold Counting what he got back wouldn't tell him if there were more rows available than he asked for. – Bobby

@Bobby Hence my asking for clarification about the meaning of "took effect"? – Waterloo

TOP without ORDER BY is pretty pointless - you'll get the TOP rows ordered by what?!?! – Oospore

@jonnyGold I think it's clear what he's asking for. He's running a query that uses TOP xxx and he'd like to know if there's a way to tell if that TOP clause actually limited the result set or not. (i.e. if his query just happened to return 5 rows, or there were more rows but the TOP limited it) – Bobby

@Oospore I don't think that's really the point of his question -- it's just a dummy query to illustrate his point. The presence of an ORDER BY wouldn't affect what he's asking. – Bobby

My main concern is limiting the time a search may run. If I give it a TOP 5000 even without an ORDER BY it will stop the query from running too long and still provide usable information to client. I need to notify the client when that has occurred with a return code. – Misbehavior

Answer 1

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.

Asafetida

wow. It's been a long day. Not sure how that didn't cross my mind. – Misbehavior

Update/details: Because I want to configure the actual max result size on the server I'm returning @N as an output param to the client. This allows the client to compare @N to the result count and display a message if need be. This also allows patching to be much easier because of the distributed nature of the client. – Misbehavior

Answer 2

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 

Satiated

@AaronBertrand No, because his WITH only selected 6 rows. – Bobby

@Bobby Right, but the OP's WITH might not. I would probably demonstrate this using a variable. – Cloudcapped

@AaronBertrand - The OP's should have a TOP 6 (or TOP N+ 1) so it can stop scanning after the 6th row. – Satiated

@AaronBertrand Well that TOP 6 is part of the logic Martin is suggesting. Martin, perhaps add a line to clarify that point? – Bobby

Wouldn't the CASE clause be evaluated for every result record? – Waterloo

@jonnyGold - Yes but it only does the COUNT once – Satiated

I see what you did here and maybe I should have been more clear with my example. I'm thinking of capping at 5000 to prevent the query from running for hours. Creating a temporary table does solve my first question and I like your solution but it may bring up a performance issue in itself. I'll most likely need to do some testing for that. – Misbehavior

@Misbehavior why do you think a temporary table won't introduce similar (or worse) performance issues? – Cloudcapped

@aaron. I misspoke. Temporary result set. Would that cause a performance hit? – Misbehavior

@Misbehavior - I would have thought Carl's answer will be more efficient TBH. It avoids the need for SQL Server to add a spool to the plan. – Satiated

Answer 3

You could append a boolean value into the query that returns true when COUNT(*) of a subquery with the same logic returns a value greater than 5 and false when it returns a value 5 or less.

This would add the information you need to the data being returned. If you are concerned about performance, and the query is extremely complicated, than this may not give a large improvement over separate queries, but should give at best a minor improvement.

Basir

Answer 4

There is no way to do that without two queries or doing performance killing recacluations...

Select TOP 5 * From Stuff

and

Select Count(*) From Stuff

Waterloo

Not true, COUNT() OVER() can do this – Cloudcapped

@AaronBertrand I'd like to see the query plans to see which way is more effecient. – Waterloo

Feel free. Of all the solutions presented thus far, when I execute all together, Carl's and Martin's come out to 4% of the overall cost each. RedFilter's is 1% more at 5%, HLGEM's is 42%, and your suggestion combined for the other 44%. I did my tests against sys.all_columns. – Cloudcapped

Answer 5

SELECT TOP 5 Field1, field2, recordcount 
FROM Stuff
CROSS JOIN  (SELECT  COUNT(*) as recordcount FROM Stuff) a
ORDER BY Field1

Perlaperle

Note that I removed the SQL antipatterns of using SELECT * and not using an ORDER BY when getting the TOP N records. – Perlaperle

Answer 6

select top 5 *,
    case when count(*) OVER() <= 5 then 'yes' else 'no' end as AllRecordsReturned
from supportContacts

Sonya