How to retrieve the total row count of a query with TOP

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

From stackoverflow

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

• 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.

  John : 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 : OH, I just answered my own question...SQL_CALC_FOUND_ROWS and FOUND_ROWS() does the job..thanks!

• 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 : Examples 1 and 3 are single queries and functionally identical.

  gbn : 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

• SELECT     TOP (2) *,
             (SELECT COUNT(*) AS Expr1 FROM T) AS C
  FROM         T
  

  gbn : Would this not do "SELECT COUNT(*) AS Expr1 FROM T" for every output row? In this case 10 rows

  Mufasa : 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.