What is a Common Table Expression?

A Common Table Expression (CTE) is a syntax introduced in SQL Server 2005 that allows an in-line reference of a query as though it were a static table or view. MSDN defines it as a "temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement". CTEs are very similar to derived tables, except that CTEs have the advantage of being reusable within the same query. This reusability opens the door to the power of recursive (self-referencing) queries. For more info on CTEs, check out this great overview article.

What does the ROW_NUMBER() function do?

Also introduced in SQL Server 2005, the ROW_NUMBER() function returns the sequential number of a row within a query's result set, starting at 1 for the first row. When used in its most common form as a derived column, it mostly behaves as if it were an "in-line identity column" scoped only for that particular query. For the first row in the result set, ROW_NUMBER() evaluates to 1, for the second row it evaluates to 2, and so on.

What does a CTE have to do with Paginating Records in a SQL query?

Since the first requirement to paginating records is to know the position of each record, ROW_NUMBER is the obvious choice since it gives you that position value with very little effort, especially when contrasted with the necessary workarounds required in earlier versions of SQL Server. However, the second requirement to paginating records it to be able to select a specified range of row positions. Sounds like a simple WHERE statment utilizing a BETWEEN, right? Well, unfortunately, the ROW_NUMBER() function cannot be referenced directly in the WHERE clause. If you try to run the following query:

SELECT ProductID, Name, ROW_NUMBER() OVER(ORDER BY ProductID)
FROM Product
WHERE ROW_NUMBER() OVER(ORDER BY ProductID) BETWEEN 11 AND 20

... it will fail with the error "Windowed functions can only appear in the SELECT or ORDER BY clauses".

Okay, no big deal, we'll just alias the ROW_NUMBER() derived column and reference it in the WHERE clause like this:

SELECT ProductID, Name, ROW_NUMBER() OVER(ORDER BY ProductID) AS RowNumber
FROM Product
WHERE RowNumber BETWEEN 11 AND 20

... whoops, that failed too with the error "Invalid column name 'RowNumber'", as would most aliased SQL functions.

So, it appears that we need some sort of work-around to get what we want.

This is exactly where the CTE comes into play:

WITH PagingCTE
AS
(
    SELECT ProductID, Name, ROW_NUMBER() OVER(ORDER BY ProductID) AS RowNumber
    FROM Product
)
SELECT ProductID, Name, RowNumber
FROM PagingCTE
WHERE RowNumber BETWEEN 11 AND 20

... which does fulfill our second and final requirement for pagination!

We could have also used a derived table instead of a CTE, but then I wouldn't have had an excuse to explain CTEs. :-)

Now you have a back-end query to compliment an ASP.NET DataGrid control and it's paging functionality. Oh, and probably other uses, as well. ;-)

If you found this post helpful, please "Kick" it so others can find it too: