ROW_NUMBER and OVER in SQL Server Paging

In SQL Server 2000 paging was very diffecult, but with SQL Server 2005 new function ROW_NUMBER() and OVER have made life easy.

Declare @Top int
Declare @Start int
SET @Top = 5
SET @Start = 10
SELECT TOP(@Top) * FROM
(
select row_number() over(ORDER by LastName) as num, OrderNumber  from Customers
) as tbl
WHERE tbl.num > @Start

The above code will get you top 5 records from Customers table starting at row number 10, you can use it in stored procedure to retrive rows.

@Top = number of rows.
@Start = Starting row number



 3/19/2008


Add Comments
Name: *
Email: *
URL:
Comments: *
 


Advertisements

MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.

SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.

SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

 I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more features and make it easy for me to update contents on regular basis.