Almost every business application I have worked with, needed some sort of paging requirement to display information in the presentation layer. At any point of time, users will be looking at a small number of records (say 25 or 50) in most cases. Some of the applications I have worked with, did the paging at the client side and the others did it at the server side.
Most people find it very easy to handle the paging at the client side. Most of the UI controls (such as a grid control) can take a large data set and handle the paging for you. However, the downside of this approach is that, almost always you will be fetching a huge amount of unwanted data from the database and it will affect the performance of the database server as well as the application.
A more elegant approach is to fetch only the data that is needed for the current page. For example, if your page size is 50 records, and if the user is on the 3rd page, you need to fetch only records 101 to 150 from the database to fill the current page. This approach is found to be performing better in most of the applications I am working on.
Server Side Paging with SQL Server 2005/2008
SQL Server 2005 introduced a number of new functions such as ROW_NUMBER which has made the paging much easier. I have written an article at sqlservercentral which explains how to write queries to support server side paging. This was followed by two excellent articles by Paul White explaining how to optimize queries that use server side paging. I suggest reading the following articles.
- Server Side Paging With SQL Server 2005 – By Jacob Sebastian
- Optimising Server-Side Paging - Part I – By Paul White
- Optimising Server-Side Paging - Part II – By Paul White
In most cases a real-world query that does server side paging will be very very complex.
New TSQL Syntax to support Paging queries in SQL Server CE 4.0
A few days ago, I came across this blog post by SQL Server CE program manager Ambrish Mishra which mentioned a new TSQL syntax introduced in SQL Server CE 4.0 which makes the paging queries much easier. I found it very exciting and spent some time exploring it.
Fetch rows 21 to 30
The following query fetches rows 21 to 30 from the Orders Table.
SELECT
*
FROM Orders
ORDER BY OrderID
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
Skip the first 5 rows and fetch the rest
The following query skips the top 5 records and fetches all other rows
SELECT
*
FROM Orders
ORDER BY OrderID
OFFSET 5 ROWS;
I found the new TSQL syntax very easy to use and quite efficient. Looking forward to see the same feature getting added into other SQL Server versions soon.
Downloading and Installing SQL Server CE 4.0 CTP 1
You can download SQL Server CE 4.0 CTP 1 from the Microsoft Download Center. Note that SSMS 2008 R2 does not support SQL Server CE 4.0 yet. I am using Microsoft Webmatrix Beta for the demos presented above. You can download Webmatrix from here. See this excellent Webmatrix tutorial by Scott Guthrie.