5

I have a SQL Server 2008 query

SELECT TOP 10 *
FROM T
WHERE ...
ORDER BY ...

I'd like to get also the total number of the rows. The obious way is to make a second query

SELECT COUNT(*)
FROM T
WHERE ...
ORDER BY ...

Is there an efficient method?

Thanks

gbn
  • 422,506
  • 82
  • 585
  • 676

6 Answers6

4

What is in this answer seems to work:

https://stackoverflow.com/a/19125458/16241

Basically you do a:

SELECT top 100 YourColumns, TotalCount = Count(*) Over()
From YourTable
Where SomeValue = 32

TotalCount will have the total number of rows. It is listed on each row though.

When I tested this the query plan showed the table only being hit once.

Community
  • 1
  • 1
Vaccano
  • 78,325
  • 149
  • 468
  • 850
  • The execution plan does show that this query hits the table only once, but it is way slower than any of the options given in the [answer by gbn](https://stackoverflow.com/a/610974), for reasons unknown to me. Your code however looks simpler, and is better suited for using with large queries already existing in the project. – Sнаđошƒаӽ Jul 01 '18 at 07:59
4

Do you want a second query?

SELECT TOP 10
    *, foo.bar
FROM
    T
    CROSS JOIN
    (SELECT COUNT(*) AS bar FROM T WHERE ...) foo
WHERE
    ...
ORDER BY
    ...

OR

DECLARE @bar int
SELECT @bar = COUNT(*) AS bar FROM T WHERE ...
SELECT TOP 10
    *, @bar
FROM
    T
    CROSS JOIN
    (SELECT COUNT(*) AS bar FROM T WHERE ...) foo
WHERE
    ...
ORDER BY
    ...

Or (Edit: using WITH)

WITH cTotal AS
(
    SELECT COUNT(*) AS bar FROM T WHERE ...)
)
SELECT TOP 10
    *, cTotal .bar
FROM
    T
WHERE
    ...
ORDER BY
    ...
gbn
  • 422,506
  • 82
  • 585
  • 676
  • I'd like to avoid a second query. Maybe with WITH instruction or temp tables? I'm not expert of sql server –  Mar 04 '09 at 15:13
  • Examples 1 and 3 are single queries and functionally identical. – gbn Mar 04 '09 at 15:16
  • Ok, I mean I'd like to avoid a second "table scan". I'd like to make the engine to scan only once the table, in order to retrieve the total count and the n rows. –  Mar 04 '09 at 15:18
  • You can't. They are 2 different query constructs. The COUNT will actually be quite efficient, probably more so than the main query because it will use the most efficient – gbn Mar 04 '09 at 15:20
1

Remove the ORDER BY clause from the 2nd query as well.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
1

No.

SQL Server doesn't keep COUNT(*) in metadata like MyISAM, it calculates it every time.

UPDATE: If you need an estimate, you can use statistics metadata:

SELECT  rows
FROM    dbo.sysindexes
WHERE   name = @primary_key,

where @primary_key is your table's primary key name.

This will return the COUNT(*) from last statistics update.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • are you saying in mysql, you can do "SELECT * FROM t_user WHERE...LIMIT 0,20" and there's meta data for the total number of rows in memory? So that I don't have to make a second query with COUNT()? – John Mar 04 '09 at 15:09
  • OH, I just answered my own question...SQL_CALC_FOUND_ROWS and FOUND_ROWS() does the job..thanks! – John Mar 04 '09 at 15:14
0
SELECT     TOP (2) *,
           (SELECT COUNT(*) AS Expr1 FROM T) AS C
FROM         T
Learning
  • 8,029
  • 3
  • 35
  • 46
  • Would this not do "SELECT COUNT(*) AS Expr1 FROM T" for every output row? In this case 10 rows – gbn Mar 04 '09 at 15:21
  • 1
    I haven't profiled this, but I think this would actually be slower since it is in a sub-query and returned in each row. I'm no guru though, the server might be smart enough to cache it. Even still, I don't think it would be any *faster* than two queries since it is still running them both. – Jon Adams Mar 04 '09 at 15:22
0

Just thought -

Implement stored procedure with below parameters:

  1. @TotalCount (default 0)
  2. @PageSize
  3. Required filter parameters.

Return 2 result set at first time.

  1. Total Count SQL statement - Return as @TotalCount (execute only if Input parameter TotalCount = 0)
  2. Top @PageSize count as required using @TotalCount

Hope this will work. Not tried but logically it will work.

Sachin Karche
  • 121
  • 1
  • 9