Many times while developing our applications we feel the need of pagination, where our User Interface (UI) has to list a number of records and fetching them all at once and listing is not a feasible option because of the following reasons -
- High utilization of the network bandwidth which if on a higher side might even choke up the bandwidth.
- User is not guaranteed to see the latest details in a multi user environment.
- High need of RAM on local machine for caching/processing.
So, the solution which is generally implemented in this situation was to fetch only the relevant records from the backend. Until Denali the following were the options used to counter this situation -
- Before SQL 2005 – ORDER BY clause in combination with TOP
- From SQL 2005 onwards – ROW_NUMBER() function with a WHERE clause
And from Denali, we can use ORDER BY clause in combination with OFFSET and FETCH
Let’s see how we can get the same output using all of the 3 ways explained above and try to fetch records from 3 to 4 assuming page size to be 2.
Total Records are as under
1. ORDER BY + TOP
2. ROW_NUMBER() + WHERE
3. FETCH + OFFSETOutput
I did a small test using all the 3 ways and have found the Denali (OFFSET and FETCH) way the best performing one followed by the ROW_NUMBER().
I would prefer using the Denali way just for 2 simple reasons -
- Simplicity of code
- Better performance
- The Denali code is based on SQL Server Denali CTP 1 and might change after further releases.