This wiki is a transcript of a previously recorded video.

Related content assets:

Simplified Result Set Paging with SQL Server 2012

Hi folks, I’m Greg Low. I’m a SQL Server MVP and part of Microsoft’s Regional Director Program. In this demo I want to show you the simplified way we can page result sets using SQL Server 2012.

Let’s pop into Management Studio. If we look at the traditional way that we would have executed these types of queries, I’ve created a stored procedure here working against the AdventureWorks database. This is a more traditional method. We need some way of retaining an order in the rows and then being able to select the rows that we want. Since SQL Server 2005, this has been relatively straightforward because we’ve had common table expressions or CTEs added to the product. We can use the ROW_NUMBER() function to give a value for a specific row to each row of data being returned in the query.

So if we look here I’ve got a procedure, GetPagedProducts_Traditional. I’ve got a PageNumber being passed as an integer and then saying this is the number of rows we want to have allocated in each page. The way we do that, I’ve said with ordered rows and this is my CTE, select row number of ProductID, which is the order I want the query coming out in. Then list the other columns I want to return. That’s basically a set of data that will come back from the CTE. Let’s just change to that database. The other thing I’ll do if I just executed that query on its own, then you can see we just assigned a row number down beside each row of the query. Now once we’ve done that, the value returned from the CTE, I can select that detail and then I’d simply need to add a WHERE clause that says look, let’s go off and calculate how we would find that particular page number of data.

That’s relatively straightforward and let’s just create that proc. But if we compare that to the new code this is a far better outcome. Again I’ve got the same header, but I’ve said GetPagedProducts in a paged sense, rather than the traditional. I’ve got the same page number and the same number of rows per page but if you look at the code here I’ve simply said select what I want, order by the ProductID and then said OFFSET and calculated the number of rows in that we wanted to move. Then said fetch this next set of rows to return the values.

Now even just a simple comparison of the code can see this is far clearer in terms of telling SQL Server what it is we’re trying to achieve. The outcome is the same, and this is a far more elegant piece of code. So if we execute the two of these we can see that it will actually return the same number of rows, but in this case I’m having a look at the query plan and already you’ll notice that there is a substantial difference in the query plan. Let’s execute the query. The main aim isn’t to really aim for performance at this stage. The main reason that these changes have been made to the product is to be able to clean up and clarify the code. Down the track, now that you’re being declarative about what you want rather than how to achieve it, the SQL Server Query Optimizer Team now have far more chance to go on and make that even better in the future.

Return to SQL Server 2012 Developer Training Kit BOM (en-US)