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:

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)

About these ads

5 thoughts on “SQL SERVER – Server Side Paging in SQL Server Denali – Part2

  1. 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

  2. Pingback: SQL SERVER – Server Side Paging in SQL Server CE (Compact Edition) « Journey to SQLAuthority

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s