21.03.2009

Paging Algorithm for Webpages with SQL Server 2000 Compability

use Northwind
go
if exists(select * from sysobjects
where name = 'usp_GetCustomerByPageNumber')
drop proc usp_GetCustomerByPageNumber
go
create proc usp_GetCustomerByPageNumber
(
@pageIndex int = 1
)
as
declare @rowCount int
set @rowCount = 10 * @pageIndex

CREATE TABLE #temp(
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL,
[Phone] [nvarchar](24) NULL,
[Fax] [nvarchar](24) NULL,
)

set rowcount @rowCount
insert into #temp
select * from Customers
order by CustomerID desc


CREATE TABLE #temp2(
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL,
[Phone] [nvarchar](24) NULL,
[Fax] [nvarchar](24) NULL,
)


insert into #temp2
select top 10 * from #temp
order by CustomerID asc

select * from #temp2 order by CustomerID desc
set rowcount 0
drop table #temp2
drop table #temp



Another Server 2000 paging algorithm is on Pinal Dave’s blog which topic is SQL SERVER - T-SQL Paging Query Technique Comparison - SQL 2000 vs SQL 2005 .


I researched why set rowcount is slow, i found out that set rowcount sorts all the table’s rows in tempdb so be careful when you are using it.

Technorati Etiketleri: ,,

Hiç yorum yok: