Demo: Simplified Result Set Paging with SQL Server 2012
- Posted: Mar 07, 2012 at 10:17 PM
- 20,852 Views
- 2 Comments
This demo shows how to replace code that was based on CTEs and ROW_NUMBER() to provide query pagination in earlier versions of SQL Server, to use the new OFFSET and FETCH clauses provided by SQL Server 2012.
Dr Greg Low
SQL Server MVP and Microsoft RD
Comments Closed
Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation,
please create a new thread in our Forums,
or
Contact Us and let us know.
Follow the Discussion
Oops, something didn't work.
What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in. You need to be signed in to Channel 9 to use this feature.What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in and view them all on your notifications page.sign up for email notifications?
Check out all the related decks, demos and labs at http://bit.ly/sql2012cookbook.
Quite often to do pagination effectively, you also need to have the total number of rows that the query would return without implementing the pagination. The way to do this currently with MSSQL is to basically run the same query twice, but with one that doesn't use the WHERE clause to alter your result count.
MySQL lets you do this much more succinctly, for example.
SELECT SQL_CALC_FOUND_ROWS Column1, Column2, ColumnN, ...
FROM myTable
JOIN my2ndTable
JOIN ...
WHERE <complex where clause>
ORDER BY <complex order by>
LIMIT (@numItemsPerPage)
OFFSET (@numItemsPerPage * (@page - 1))
-- 10 results (if @numItemsPerPage were 10)
SELECT FOUND_ROWS() as 'Total Rows'
-- 'Total Rows' = 380
Remove this comment
Remove this thread
close