Geeks With Blogs
Chris Breisch   .NET Data Practices
Search this Blog!

There's a good article here by Frank Kerrigan on a way to implement paging in SQL Server 2005 by using Common Table Expressions (CTE's).

What are CTE's?  This is a new feature of SQL Server 2005 that helps you eliminate temp tables and cursors in your queries.  You can generate some pretty amazing queries using recursive CTE's, but that's not what Frank is trying to demonstrate.  Anyway, they can be used similarly to a derived table.

Here's the syntax:

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

Here's an example of a CTE that uses the AdventureWorks database and returns the total number of sales order and most recent sales order date fo rall salespersons and their managers.  It demonstrates how you can use a single CTE multiple times in a query.

 

WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate) 
AS 
(
 SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
 FROM Sales.SalesOrderHeader
 GROUP BY SalesPersonID 
) 
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
 E.ManagerID, OM.NumberOfOrders, OM.MaxDate 
FROM HumanResources.Employee AS E
 JOIN Sales_CTE AS OS
 ON E.EmployeeID = OS.SalesPersonID
 LEFT OUTER JOIN Sales_CTE AS OM
 ON E.ManagerID = OM.SalesPersonID 
ORDER BY E.EmployeeID; 

So, Frank uses this kind of syntax and the ROW_NUMBER() function to cleverly implement paging:

With Cust AS
    ( SELECT CustomerID, CompanyName,
    ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
    FROM Customers )
select *
from Cust
Where RowNumber Between 20 and 30

As they say, read the whole thing.

Posted on Wednesday, May 9, 2007 12:27 PM Database Practices | Back to top


Comments on this post: Using Common Table Expressions to Implement Paging in SQL Server 2005

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Chris J. Breisch | Powered by: GeeksWithBlogs.net