While working on thatstoday.com I found a few situations where I needed pagination to show X items per page, with a navigation at the bottom of the page.
In SQL Server 2005/2008 there is a CTE called WITH. WITH, is used for creating temporary named result sets that can be queried. Using the With expression, allows for the simplification of query logic, by allowing the separation of logic into separate steps. The WITH expression has a few basic parts:
- WITH [name of temporary resultset] (columns in result set)
- AS ( SQL Query Definition )
Combining With, As and Between we can select a part of the result set to achieve the pagination function.
SQL Example:
Declare @PaginationPage int, @PaginationPerPage int
Set @PaginationPage=1;
Set @PaginationPerPage=10;
SET NOCOUNT ON;
-- create the CTE
With Posts as
(
SELECT ROW_NUMBER() OVER(ORDER BY CreateDate desc) AS RowNum, m.*
FROM Members m
)
-- select a part of the CTE
SELECT * FROM Posts
WHERE RowNum BETWEEN (@PaginationPage - 1) * @PaginationPerPage + 1
AND @PaginationPage * @PaginationPerPage
ORDER BY CreateDate desc;
-- a secondary select to get the the max item count
select COUNT(*) from Members
CTE’s compared to Temporary and Variable Tables
A CTE can only be called once, and then it is “consumed”, compared to a temporary table, which can be used over and over. A CTE can also be recursive, which would normally be faster than trying to do something with a temporary table. A non-recursive CTE is essentially a view that exists only for the statement it is part of.
Temporary tables are just like normal tables, but are created in the TempDB database. They exist until they are dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that procedure calls. You can also use Table variables that are created when they are declared and are dropped when they go out of scope. Table variables are also stored in TempDB. These cannot be indexed.
