… Implement pagination using CTE in SQL Server 2005

Paging is a technique where you return a selected set of rows in a ordered manner, for e.g. records from 1-10, 10-20 or 3-7 etc. You must have seen this technique while accessing your mail on say HotMail or Yahoo or GMail. They provide you with a list of mails which have from 1-10 and then you have to click on next button to get the next set of mails.

Paging is a good idea on web pages, when you have a large number of records. If you were to list all the records in your table and there are thousands of records, it may take a little while before the page loads, which is, I would say detrimental to your business. More responsive site makes for more visitors.

There are many methods which you can be used to return data in pages. The method which I am going to discuss here is using Common Table Expressions (CTE) a new concept introduced in SQL Server 2005. You can learn more about the topic on MSDN Magazine Oct 2007(Common Table Expressions) or on MSDN at Using Common Table Expressions. Without going to much details lets proceed with our task.

For this little demonstration lets consider a small example.

Consider you have a database which stores all the information for a small establishment. For our example we shall consider the table product. This table contains the product code and the product name (we have kept it very simple ) and we are going to show a subset of products (considering there are large number of products in the inventory)

–Product table schema
CREATE TABLE Product_Catalog
(
    Product_Code NVARCHAR(5),
    Product_Name NVARCHAR(15)
)

–Insert some information into the product table
INSERT INTO Product_Catalog VALUES('HTS01', 'Soap')
INSERT INTO Product_Catalog VALUES('HTP01', 'Cleanex')
INSERT INTO Product_Catalog VALUES('HTSH9', Gillette)
INSERT INTO Product_Catalog VALUES('HTST9', 'Colgate')
INSERT INTO Product_Catalog VALUES('HTSB7', 'Oral G')
INSERT INTO Product_Catalog VALUES('HTST4', 'Close Up')
INSERT INTO Product_Catalog VALUES('HTS06', 'Palmolive')

 Now that we have a basic schema ready, lets go ahead and implement the logic for paging. Before we go ahead we need some more information like the page no (the start of the subset) and page size(the no of records in a set)

for e.g. if you have 900 records(an arbitrary no) then if you set the page size as 50, then we are going to show the following subset 1-50, 51-100, 101-150 so on and so forth. So in the above example 1-50 denotes page no #1, 51-100 denotes page no #2 so on and so forth.

—Declare variable which are going to be used for paging

DECLARE @PageNo INT
DECLARE @PageSize INT
DECLARE @PageRecordStart INT
DECLARE @PageRecordEnd INT

SET @PageNo = 1
SET @PageSize = 4

SET @PageRecordStart = ((@PageNo - 1 ) * @PageSize) + 1
SET @PageRecordEnd = @PageNo * @PageSize

ok so we have all the information we need, lets get down to the grind. Assuming you are already familiar with CTE the rest of the topic will be very easy.

And this is the final selection

;WITH Product_page (RowNo, Product_Code, Product_Name) AS
(
    SELECT Row_Number() OVER (ORDER BY Product_Code) AS RowNo,
            Product_Code as Product_Code,
            Product_Name as Product_Name
    FROM Product_Catalog
)
SELECT Product_Code, Product_Name
FROM Product_page
WHERE RowNo BETWEEN @PageRecordStart and @PageRecordEnd

We have used the ROW_NUMBER() function to provide us with sequential numbers for our range selection, its like having a identity column with seed value 1. Notice we have used the “BETWEEN” operator to give us records within a specified range denoted by PageRecordStart and  PageRecordEnd .

That is all there is for using pagination. Use pagination for a more responsive web pages. Happy querying.

---

Possibly related posts: (automatically generated)

• HOW-TO: Search string value contained in comma seperated string
• Self Joins
• MERGE command in SQL Server 2008
• Insert values into IDENTITY column of SQL server table / Disable IDENTITY C…