Efficient Way of Getting @@Rowcount from a Query Using Row_Number

Efficient way of getting @@rowcount from a query using row_number

Check out the COUNT(*) aggregate when used with OVER(PARTITON BY..), like so:

    SELECT
     ROW_NUMBER() OVER(ORDER BY object_id, column_id) as RowNum
    , COUNT(*) OVER(PARTITION BY 1) as TotalRows
    , * 
    FROM master.sys.columns

This is IMHO the best way to do it without having to do two queries.

CTE, ROW_NUMBER and ROWCOUNT

In T-SQL it should be

;WITH Props AS
(
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY PropertyID) AS RowNumber
    FROM Property
    WHERE PropertyType = @PropertyType AND ...
)

, Props2 AS
(
    SELECT COUNT(*) CNT FROM Props
)

-- Now you can use even Props2.CNT
SELECT * FROM Props, Props2
WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND (@PageNumber * @PageSize);

now you have CNT in every line... Or you wanted something different? You wanted a second resultset with only the count? Then do it!

-- This could be the second result-set of your query.
SELECT COUNT(*) CNT
FROM Property
WHERE PropertyType = @PropertyType AND ...

Note: reedited, the query 1 David was referencing now has been trashcanned, query 2 is now query 1.

Return total records from SQL Server when using ROW_NUMBER

I typically do it this way - never really checked whether it's very efficient from a performance point of view:

WITH YourCTE AS 
(
   SELECT 
       (list of columns),
       ROW_NUMBER() OVER (ORDER BY ......) AS 'RowNum' 
   FROM dbo.YourBaseTable
)
SELECT 
    *,
    (SELECT MAX(RowNum) FROM YourCTE) AS 'TotalRows' 
FROM 
    YourCTE
WHERE   
    RowNum BETWEEN 101 AND 150

Basically, the RowNum value will have values 1 through the total of rows (if you don't have a PARTITION BY in your CTE) and thus selecting MAX(RowNum), you get the total number of rows.

Fastest way to count exact number of rows in a very large table?

Simple answer:

• Database vendor independent solution = use the standard = COUNT(*)
• There are approximate SQL Server solutions but don't use COUNT(*) = out of scope

Notes:

COUNT(1) = COUNT(*) = COUNT(PrimaryKey) just in case

Edit:

SQL Server example (1.4 billion rows, 12 columns)

SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK)
-- NOLOCK here is for me only to let me test for this answer: no more, no less

1 runs, 5:46 minutes, count = 1,401,659,700

--Note, sp_spaceused uses this DMV
SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'MyBigtable' AND (index_id < 2)

2 runs, both under 1 second, count = 1,401,659,670

The second one has less rows = wrong. Would be the same or more depending on writes (deletes are done out of hours here)

sql row_number() vs select row_number() to get data

Writing a subquery without a from clause is a waste of a subquery. It doesn't do anything desirable.

Just call the function directly.

Note: This advice applies to any expression in a subquery, not only row_number().

Why does your subquery return only "1"s? That reason is rather subtle to explain. But, imagine that the subquery were written as:

select *, 
       (select ROW_NUMBER() OVER (Partition by amount order by id) a
        from dual)  

This is, in fact, how the query could or would be written in several SQL databases. dual is a table with exactly one row. That little fact emphasizes what is happening. The subquery is referring to one row at a time. Hence, the row_number() that you are getting refers only to partitioning by that row. Voila! You only get "1".

I realize that it might help if you considered the subquery as:

select *, 
       (select ROW_NUMBER() OVER (Partition by amount order by id) a
        from (select products.amount, products.id) p
       )  

This emphasizes that the row_number() is being applied over the result set of the sub-select. That sub-select -- by definition -- has only one row, which results in the "1" being returned.

Getting total row count from OFFSET / FETCH NEXT

You can use COUNT(*) OVER() ... here is a quick example using sys.all_objects:

DECLARE 
  @PageSize INT = 10, 
  @PageNum  INT = 1;

SELECT 
  name, object_id, 
  overall_count = COUNT(*) OVER()
FROM sys.all_objects
ORDER BY name
  OFFSET (@PageNum-1)*@PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;

However, this should be reserved for small data sets; on larger sets, the performance can be abysmal. See this Paul White article for better alternatives, including maintaining indexed views (which only works if the result is unfiltered or you know WHERE clauses in advance) and using ROW_NUMBER() tricks.

How to return total number of records with TOP * select

There are a lot of simple answers to this question, as other posters have pointed out. There is also a lot of subtlety depending on your scenario. There is a fairly in depth discussion of the issue @ Efficient way of getting @@rowcount from a query using row_number

How do I get rowcount of a cte in a separate dataset?

Without using a temp table first, I'd use a CROSS JOIN to reduce the risk of row by row evaluation on the COUNT

To get total row, this needs to happen separately to the WHERE

WITH cteCustomers
     AS ( SELECT
            id,
            Row_Number( ) OVER(ORDER BY Age DESC) AS Row
          FROM   @Customer
          WHERE  employed = 1 
     /*Imagine I've joined to loads more tables with a really complex where clause*/
     )       

SELECT
  name,
  age,
  Total
FROM       cteCustomers
INNER JOIN @Customer cust
  /*This is where I choose the columns I want to read, it returns really fast!*/
  ON cust.id = cteCustomers.id

CROSS JOIN

(SELECT Count( *) AS Total FROM   cteCustomers ) foo

WHERE      row BETWEEN ( @PageSize * @PageNumber - 1 ) AND ( @PageSize * ( @PageNumber ) )
ORDER      BY row ASC

However, this isn't guaranteed to give accurate results as demonstrated here:

can I get count() and rows from one sql query in sql server?

Edit: after a few comments.

How to avoid a CROSS JOIN

WITH cteCustomers
     AS ( SELECT
            id,
            Row_Number( ) OVER(ORDER BY Age DESC) AS Row,
            COUNT(*) OVER () AS Total --the magic for this edit
          FROM   @Customer
          WHERE  employed = 1 
     /*Imagine I've joined to loads more tables with a really complex where clause*/
     )       

SELECT
  name,
  age,
  Total
FROM       cteCustomers
INNER JOIN @Customer cust
  /*This is where I choose the columns I want to read, it returns really fast!*/
  ON cust.id = cteCustomers.id
WHERE      row BETWEEN ( @PageSize * @PageNumber - 1 ) AND ( @PageSize * ( @PageNumber ) )
ORDER      BY row ASC

Note: YMMV for performance depending on 2005 or 2008, Service pack etc

Edit 2:

SQL Server Central shows another technique where you have reverse ROW_NUMBER. Looks useful

---

Related Topics