Monday, March 12, 2012

Paging in SQL Server

Hiii all

SQL Server 2000 or 2005 dose not support the LIMIT statement like mySQL. So plz can anyone tell me tht how to do paging in SQL Server? Without using CLR Integration...

Hi,

First, I'd said LIMIT keyword is not supported in T-SQL.

To paginate data on SQL Server 2000, you can read that article describing few solutions :

http://www.codeproject.com/aspnet/PagingLarge.asp

SQL Server 2005 introduce some T-SQL enhancements to support pagination with function ROW_NUMBER().

Here is how to use it paginating Contact table of AdventureWorks database.

Code Snippet

With Contacts As

(

SELECT c.FirstName, c.LastName

,ROW_NUMBER() OVER(Order By Firstname) AS 'RowNumber'

FROM Person.Contact c

)

Select * From Contacts

where RowNumber Between 10 and 20

Jean-Pierre Riehl

http://blog.djeepy1.net

|||Wrong forum. Moving to Transact-SQL from SSIS.

No comments:

Post a Comment