Select TOP N and set variable if more could be selected

Question

Is there a more efficient way of doing the following SQL?

I want to select the top 50 results, but I also want to set a variable to tell me if I would have gotten more results back without the TOP

DECLARE @MoreExists BIT
SET @MoreExists = 0

DECLARE @Count INT
SELECT @Count = Count(*) 
   FROM MyTable WHERE ... --some expensive where clause

IF @Count > 50
    SET @MoreExists = 1

SELECT TOP 50 Field1, Field2, ... 
    FROM MyTable WHERE ... --same expensive where clause

Answer 1

Select 51 results instead, use the top 50 in the client layer, and use the count to know if there are more.

Answer 2

Yes.

The common approach is to use ROW_NUMBER():

WITH MyTableEntries AS 
( 
    SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row, col1, col2 
    FROM MyTable
    WHERE
          -- some expensive WHERE clause
)
SELECT col1, col2
FROM MyTableEntries 
WHERE Row BETWEEN(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

The efficient approach shown at this SqlServercentral article:

DECLARE @startRow INT ; SET @startrow = 50
;WITH cols
AS
(
    SELECT table_name, column_name, 
        ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq, 
        ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
    FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDERBY seq

Answer 3

A spin on @Dougs answer

SET NOCOUNT ON 

SELECT TOP 51 Field1, Field2, ... 
    into #t
    FROM MyTable WHERE ... --same expensive where clause

if @@rowcount > 50 
      SET @MoreExists = 1

 SET NOCOUNT OFF

 SELECT TOP 50 Field1, Field2, ... 
    from #t
    -- maintain ordering with an order by clause

Answer 4

How about using COUNT(*) OVER... in a sub-query?

DECLARE @ReqCount int;
SET @ReqCount = 50;

SELECT TOP (@ReqCount) *
FROM 
(
SELECT *, Count(*) OVER() AS TotalCnt
FROM MyTable WHERE ...
) t
ORDER BY ...
;

And if you want to use ROW_NUMBER() too, then try:

SELECT *
FROM 
(
SELECT *, ROW_NUMBER() OVER (ORDER BY ...) AS RowNum, Count(*) OVER() AS TotalCnt
FROM MyTable WHERE ...
) t
WHERE RowNum BETWEEN @StartRange AND @EndRange
ORDER BY ...
;

And then you can easily check to see if TotalCnt > @ReqCount (or @EndRange), to be able to see if there are more to fetch.

Rob