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
SQL Server DBA
I am a SQL Server DBA with almost 9 years of experience in database
technologies.
Right now I am in process of redesigning this website in ASP.NET 2.0 and AJAX. The main purpose
of new SQL DBA website is to offer more help to database professionals and make it easy for
me to update contents on regular basis.