The best part of the having blog is that SQL Community helps to keep it running with new ideas. Earlier I wrote about SQL SERVER – Server Side Paging in SQL Server 2011 – A Better Alternative. A very popular article on that subject. I had used variables for “number of the rows” and “number of the pages”. Blog reader send me email asking in their organizations these values are stored in the table. Is there any the new syntax can read the data from the table. Absolutely YES!
USE AdventureWorks2008R2
GO
CREATE TABLE PagingSetting (RowsPerPage INT, PageNumber INT)
INSERT INTO PagingSetting (RowsPerPage, PageNumber)
VALUES(10,5)
GO
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET (SELECT RowsPerPage*PageNumber FROM PagingSetting) ROWS
FETCH NEXT (SELECT RowsPerPage FROM PagingSetting) ROWS ONLY
GO
Here is the quick script:
This is really an easy trick. I also wrote blog post on comparison of the performance over here: .
SQL SERVER – Server Side Paging in SQL Server 2011 Performance Comparison
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology Tagged: SQL Paging
