"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

March 17, 2012

TSQL Enhancements in SQL 2012 - Part III

[Previous Post in Series - TSQL Enhancements in SQL 2012 - Part II]

This dedicated post is for SQL 2012 Paging feature. This is a very important feature in terms of performance impact. I have seen pagination playing a crucial load while data loading. This inbuilt paging support would help to fetch only required data.

It would be intersting to see how paging is implemented by the Database Engine. How execution Plan looks like when you run a paging query.
  • Create a Table
  • Populate a million records
  • Add Indexes on columns
  • Run Paging queries
  • Check Execution Plan
Step 1 - Creating Table and Populating Data
Step 2 - SELECT query with Filter Condition using Primary Key Column. As you can see it is a Clustered Index Seek Operation
Step  3 - Implement the Same using Paging Option. Check the Execution Plan  

Step 4 - Results of Paging Query

Since We are requesting pagination on a clustered index column, I expected this must be a seek but it seems to be a SCAN.This might be a costly operation. I hope to post answer for it in next set of posts.

More Reads
Happy Learning!!!!

No comments: