Monday, February 4, 2013

Page Wise Results from Sql Server

Getting page wise results is one of the most important factor while considering the performance of the application.  Getting a lot amount of data from database and showing the required data to the user will lack the performance of the application.  In such cases we can go for retrieving page wise results i.e., the amount of data which is required for user. the Here is the Query for Getting Page wise results from an sqlserver.

This query uses a Northwind database for giving an example.




Use Northwind

DECLARE @startrow int
DECLARE @endrow int

set @startrow=10
set @endrow=20

SELECT Mstr.* FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY CUS.CustomerID DESC) AS SlNo, CUS.CustomerID,CUS.ContactName,CUS.City FROM Customers CUS
)
Mstr WHERE SlNo BETWEEN @startrow AND @endrow ORDER BY CustomerID DESC


Thanks & Regards
Happy Coding...