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!