Geek asks and answers

Sql Server TOP - in use?

Is there a keyword or meta information in SQL Server that will tell you if the TOP effect has entered?

EX:
Select TOP 5 * From Stuff

RESULT: 5 rows

What is the best way to determine if there were 6 or more?

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

But I am worried about a separate call to get an invoice, because the actual query is much more complicated than this one on a large table.

Thank!

+5
source share
6 answers

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

+7

, . -

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
+12

, true, COUNT (*) 5 false, 5 .

. , , , .

0

...

Select TOP 5 * From Stuff


Select Count(*) From Stuff
0
SELECT TOP 5 Field1, field2, recordcount 
FROM Stuff
CROSS JOIN  (SELECT  COUNT(*) as recordcount FROM Stuff) a
ORDER BY Field1
0
select top 5 *,
    case when count(*) OVER() <= 5 then 'yes' else 'no' end as AllRecordsReturned
from supportContacts
0

More articles:


All Articles