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 Denali – 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!
CREATE TABLE PagingSetting (RowsPerPage INT, PageNumber INT)
INSERT INTO PagingSetting (RowsPerPage, PageNumber)
ORDER BY SalesOrderDetailID
OFFSET (SELECT RowsPerPage*PageNumber FROM PagingSetting) ROWS
FETCH NEXT (SELECT RowsPerPage FROM PagingSetting) ROWS ONLY
Here is the quick script:
This is really an easy trick. I also wrote blog post on comparison of the performance over here: .
Reference: Pinal Dave (https://blog.sqlauthority.com)