TSQL Enhancements in SQL11 (SQL Server 2011 / Denali) to support Server Side Paging

Server Side Paging is a common scenario most application developers must have encountered while creating front end applications that requires paging support. Instead of returning all the rows from the database and implement paging at the client side, you might want to implement the paging on the server side so that only those rows needed for the current page are retrieved from the server. This reduces the pressure on the database server as well as help the client application to perform better.

Writing a paging Query

By writing a paging query, we are essentially telling the database engine something like the following:

/*
My application displays 10 rows on a page and I want
data to display on page 3.

Send me rows 21 to 30 after ordering the customer table
by Customer ID
*/

The TSQL representation of the above query is as follows

SELECT
*
FROM @t
ORDER BY CustID  
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

Here is a fully functional sample code that you can run on a SQL Server Denali (2011) instance.

-- Create a table
DECLARE @t TABLE (
CustID INT IDENTITY,
CustName VARCHAR(100) )

-- Populate some data
INSERT INTO @t (CustName)
SELECT 'Customer ' + CAST(Number AS VARCHAR)
FROM master..spt_values WHERE type = 'p' AND number BETWEEN 1 and 100

-- Write the query

Read more: Beyond Relational

Posted via email from .NET Info