Restricting the number of returned rows in a query

[JimDiaz]

During application development there are many cases in which I am interested in providing the user with the first X (TOP X) number of rows in a query as well as provide an indication that there are more or better yet that there is a specific number number of rows. We have approached this in two different ways, example 1 says there is more, example 2 says there are x rows meeting this condition. Example 2 is preferred but I am concerned about the time it will take to process.

I was wondering if anyone had guidance on which general method is preferred or if there is a better way.

Example 1: SELECT TOP 500 ColumnName, IFNULL ( (SELECT TOP 1 START AT 501 ColumnName FROM Owner.ViewName ORDER BY ColumnName), '0', '1' ) AS MoreRowsExist FROM Owner.ViewName ORDER BY ColumnName

Example 2:
SELECT TOP 500 ColumnName, COUNT(*) OVER() AS NumberOfRows FROM Owner.ViewName ORDER BY ColumnName

Thanks,

Jim

[VolkerBarth]

Is it a requirement to use one query? I could imagine (but don't know!) the engine could optimize a separate "SELECT COUNT(*)" statement better than as part of example 2...

---

FWIW: Here's a FAQ with helpful details whether SELECT COUNT(*) is slow or not... (yes, I'm aware you don't want it to be slow:))

[Former Member]

Could do something like this, the row count would repeat on each line, but the count function itself would only fire once I believe ...

select top 500 columnname, row_cnt from tablename , (select count(*) row_cnt from tablename) as mdt ;

Where you use a derived table to get the total count and just retrieve the information from the derived table for each row returned. You could also do a count on the rows retrieved and set a flag to indicate if more rows were available. something like:

select top 500 columnname, row_cnt ,If count(*) <> row_cnt then 'Y' else 'N' endif as MoreRowsAvail ...