Paging is a process of splitting large result set into subset of pages. So, instead of displaying 5000 records resulting from a search, first 20 records are shown and then next 20 and so on.

In SQL Server 2005, Common Table Expression (CTE) is generally used to implement paging feature. In SQL Server 2012, MS has come up with a modified ORDER BY clause to implement the same. This is done using two new keywords OFFSET and FETCH. Offset tells the number of rows to skip and Fetch tells number of rows to be returned after offset clause is processed. So, offset 0, Fetch 10 will return 10 rows starting from 1 and an offset 2, Fetch 10 will return 10 rows starting from 3.

This is similar to using Limit operator in MySql.

The code in Listing 1 illustrates how it is done via Order By clause.

Listing 1

The above query returns 10 rows starting from 1 to 10. To get next 10 rows, set the @start parameter to 10 and so on.

With that being said, let’s compare the performance of this new feature with paging through CTE. The code in listing 2 shows paging via CTE.

Listing 2

The query in listing 1 took 424 ms where as the query in listing 2 executed in 457 ms. So, there isn’t any significant improvement in performance when using offset and fetch for paging. Moreover, the execution plans of both the queries are very much similar. Figure 1 and Figure 2 show execution plan for queries in listing 1 and listing 2 respectively. The only difference are the Segment, Sequence Container and filter operator which are being used to filter out rows using row count and they don’t add much to the query cost.

1_SQL_Server_2012_“Denali”_Paging_feature

Figure 1: Execution Plan for paging using offset and fetch

2_SQL_Server_2012_“Denali”_Paging_feature

Figure 2: Execution Plan for paging using a CTE

In addition to do paging, offset and fetch keyword can be used to replace top operator as shown in below query.

The performance is similar to what we get with top keyword as the top operator is used to filter out the rows as shown in the below execution plan.

3_SQL_Server_2012_“Denali”_Paging_feature

More information regarding this can be found at http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx#Offset.

Happy Learning!!!

 

Regards

Ahmad Osama

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook