Pagination And Total Number Of Rows From One SELECT

If you have a business requirement to implement a pagination in user interface, you could use OFFSET/FETCH or ROW_NUMBER() to retrieve rows from SQL Server. But when you need to show the total number of rows that can be retrieved from database, it becomes harder.

You can use two queries – one for counting the total number, second for extracting the page, but this is not a good idea. Why? First, you’ll have negative impact on performance because you need 2 queries, second, you can get different results cause data could change between these two queries.

Luckily, there are some solutions to do this in one query. Because SQL Server could not return rows and assign a value in one query, we need to put the total number as an additional column into a resultset.

1. Using COUNT(*) OVER()

It’s a very simple query to write. As for performance, I’ll compare all solutions later.

2. Using CTE to count all records

3. Extracting keys to temporary table

The idea is to extract primary keys to temporary table or CTE, get a page of rows, then join it with the original table. We need to use ROW_NUMBER() to assign ordinal number to every row in order to get a page. There are 2 ways to implement pagination.

3a. Extracting keys and filtering row numbers in WHERE

3b. Extracting keys and using OFFSET/FETCH

Now I’ll compare these queries for IO statistics and query optimizer costs.

1. Using COUNT(*) OVER() 2. Using CTE to count all records 3a. Extracting keys and filtering row numbers in WHERE 3b. Extracting keys and using OFFSET/FETCH
Select all rows IO statistics Table ‘Worktable’. Scan count 3, logical reads 4728… Table ‘sysschobjs’. Scan count 1, logical reads 25 Table ‘sysschobjs’. Scan count 2, logical reads 26 Table ‘sysschobjs’. Scan count 2, logical reads 46 Table ‘sysschobjs’. Scan count 2, logical reads 138
Query cost 15% 11% 25% 50%
Select with WHERE clause IO statistics Table ‘Worktable’. Scan count 3, logical reads 169… Table ‘sysschobjs’. Scan count 1, logical reads 4 Table ‘sysschobjs’. Scan count 2, logical reads 8 Table ‘sysschobjs’. Scan count 2, logical reads 28 Table ‘Worktable’. Scan count 0, logical reads 0… Table ‘Workfile’. Scan count 0, logical reads 0… Table ‘sysschobjs’. Scan count 3, logical reads 31
Query cost 4% 6% 35% 55%

From my viewpoint, the second solution is the best cause it needs the least number of pages to be read and it does not create a table in TempDB.

Leave a Reply

Your email address will not be published. Required fields are marked *