Wednesday, January 24, 2007

Paging Implementation With SQL

When you see how the paging is a piece-of-cake now in .NET server controls (like GridView), you should imagine how much amount of work is needed to do paging in early days. On of the suggestions was to fetch all the data block and store them on a session variable or any such temporary allocation and just do your paging in you business layer! Ok..I hear someone say what a silly solution! That is true. This is really a mess when you working on huge data blocks. So, the best way is to do you paging in your data layer.





You may ask? why I should consider paging while I have ready-to-use paging server controls? .. Well, Although, .NET provide ready-to-use controls with paging feature (like GridView), you still need to implement paging inline for other controls such as Repeaters and Datalists.

Recently, I was searching for doing paging using a single SQL statement. Unfortunatily, solutions differ from DBMS to another. For example, in MySQL, you can make the paging using a SQL statement like that:

SELECT fields FROM table LIMIT $offset,$pgsize


In the other side, SQL Server doesn't have such keywords for paging. But still there is a solution. You can go around and use "TOP" keyword to do the paging using SQL. For example, if your page size is PAGESIZE, you can divide you data to several pages as following:

SELECT TOP PAGESIZE * FROM EMP WHERE ID NOT IN (SELECT TOP OFFSET ID FROM EMP)


PAGSIZE is constant,and you just change the OFFSET. For example, if your PAGESIZE is 20, then your offset should be: 0, 20, 40 and so on. I think this SQL statement is a piece of art! I really like it!

kick it on DotNetKicks.com

8 comments:

Mohamed said...

Actually SQL Server 2005 HAS buil in paging support, using the "ROW_NUMBER" keyword.
Google for "paging sql server 2005" and you'll find much sample code on that.

Mohammad Nour said...

Nice comment! Well.. I was using SQL Server 2000 for this project and ROW_NUMBER is not option here as it wasn't provided yet in this release. I think SQL Server Team consider this paging problem in their 2005 release by providing ROW_NUMER keyword.

Rush said...

you would have to make sure all of your id's started at 1 and were sequential. not the best approach especially with 'huge data blocks'

Mohammad Nour said...

No, it doesn't depend at all on the ID sequence or numbers. If I want to retrieve TOP 10 records, this doesn't mean that IDs should be 1,2,3 ... etc. TOP retrieve only first TOP records of the query result whatever your query depends on ID column or not.

magnetik said...

That actually is quite a cute solution - nice work.

You are correct that the ID sequence doesn't matter, but what I think you should include is that some form of ORDER BY clause is needed, unless you are relying on table indexes to control your list orders.

Mohammad Nour said...

Yeah, that's absolutely correct. However, this depends in your application and the way you want to sort your data.

musa said...

Thanks a lot, this is what i was looking for for SQL 2005 usage... i made it into a stored proc, that will take a page size and page number passed form C# source.

CREATE PROC GetSearchResults
@PageSize INT,
@PageNumber INT
AS
DECLARE @OffSet INT
SET @OffSet = @PageSize * (@PageNumber - 1)
SELECT TOP (@PageSize) * FROM [Content]
WHERE ContentID NOT IN (
SELECT TOP (@OffSet) ContentID FROM [Content] ORDER BY PageTitle
)
ORDER BY PageTitle

Keybern said...

Okay, but how can you retrieve the total number of pages in the same query ? Without that you can't decently set up your interface...