SQL SERVER – Server Side Paging in SQL Server Denali – Part2

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!

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:

SQL SERVER - Server Side Paging in SQL Server Denali - Part2 paging4

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 (https://blog.sqlauthority.com)

,
Previous Post
SQLAuthority News – 18 Seconds of Fame – My PASS Experience
Next Post
SQL SERVER – ORDER BY ColumnName vs ORDER BY ColumnNumber

Related Posts

2 Comments. Leave new

  • Hi there!

    I have a question not about exacly to this article:

    To export a result of a query from the sqlserver database to excel we use:

    —————————————————————————

    INSERT INTO OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=C:Teste.xls;’, ‘select nome_inst, sexo_inst, data_nasc, sal_inst FROM [Plan1$]’)

    select nome_inst, sexo_inst, data_nasc, sal_inst from instrutor

    GO

    —————————————————————————

    Someone alread export to calc from brOffice?
    Is possible? If somebody have an example to show i thank so much.

    best regards

    Jardel

    Reply

Leave a Reply

Menu