There are certain practical scenarios when you are required to fetch tens of thousands of rows of records from a database. Most of the ASP.Net applications are connected to a SQL Server database and unlike MySQL and Oracle databases, SQL Server had no easy Paging feature till SQL Server 2012 release! There were convoluted solutions to a seemingly easier problem. So, in most of the cases the paging was implemented at the UI end using a combination of Dataset and Gridview feature.
Microsoft has fulfilled this long term demand of SQL Server developers with 2012 edition.
As first step of learning let us create Employees table. You can use the following T-SQL to create the required table for this sample. I have used SQL Server 2014 Express Edition for this article.
You will also require a few rows of records for the purpose of this article. Let us insert records using the following T-SQL. If you notice carefully as we are using a cross join it will insert 36 records. Take a note as how just 3 Insert queries are used to insert 36 records. You can use this simple technique to populate your table with records for testing.
You can download above code from below embedded frame and copy/paste directly to your SQL Server Management Studio Query Editor.
Now let us see the actuall code. Here you go:
If you execute above queries, you will understand that in the above image, the 2nd query (OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY) is equivalent to using:
SELECT TOP(5) * FROM Employess ORDER BY EmpId
The output of the 3rd query (OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY) will NOT show the EmpId already shown in the first query. I think by carefully analyzing the output of the query you must have by now realized that:
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY can be translated to Skip first 5 rows from the sorted resultset and return next 5 rows.
Also make a note of the following rules described in MSDN while using Offset-Fetch Clause:
- While using OFFSET and FETCH clause use of ORDER BY is mandatory.
- OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
- TOP cannot be combined with OFFSET and FETCH in the same query expression.
- The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.
If you’ve understood it fully you can pass two parameters namely PageNumber and RecordsPerPage in a stored procedure and use it to fetch page wise resultsets from database tables. Please leave a comment if you like to know more about this.