Paging in SQL Server 2000

15 Jul

Oh man, working with M$ is hell. Yeah, they do very big things but they just forgot small but essential things.

M$ maybe the most stupid when not implementing paging functionality in T-SQL. Most of other DBMSs do that. Paging from server is a vital point, especially in web development. If you just do paging from client-side, you are wasting most of network traffics for unused records.

After searching thouroughly on Internet, I finally found out some ways to get arround this flaw. One uses dynamic SQL query to refine the query and add some ORDER BY + TOP clauses to get the result. Another way is to use a temporary table with an identity column used as a row number. I prefer the 2nd way. At least, it’s more secured.

alter procedure GetAuthors
(
@orderby 	nvarchar(100),
@recordsPerPage int,
@pageNumber 	int
)
AS
--  the amount of rows actually needed
declare @rowsNeeded int
--  the position where we need to start retrieving records
declare @startRecord int
-- initialize @rowsNeeded
set @rowsNeeded = (@pageNumber * @recordsPerPage)
-- initalize @startRecord
set @startRecord = ((@pageNumber-1) * @recordsPerPage) + 1

create table #temptable
(
 row		 int identity(1,1),
 au_id 		 varchar(20),
 au_lname	 varchar(40),
 au_fname	 varchar(20),
 phone	 	 char(12),
 address 	 varchar(40),
 city		 varchar(20),
 state	  	 char(2),
 zip		 char(5),
 contract	 bit
) 

-- set the rowcount to only the rows we need.
set rowcount @rowsNeeded

-- select data into a temp table,  I choose to
-- select all data, not just the key into the
-- temp table to avoid another join if the table is large.
insert into #temptable
(au_id,au_lname,au_fname,phone,address,city,state,zip,contract)
select au_id,
       au_lname,
       au_fname,
       phone,
       address,
       city,
       state,
       zip,
       contract
FROM authors
ORDER BY
CASE
WHEN @orderby=’au_lname’ THEN au_lname
WHEN @orderby=’au_fname’ THEN au_fname
WHEN @orderby=’au_id’    THEN au_id
END 
ASC
select row,
       au_id,
       au_lname,
       au_fname,
       phone,
       address,
       city,
       state,
       zip,
       contract
from #temptable
where row >=@startRecord

drop table #temptable

If you use SQL Server 2005, the life must be easier. You can use ROW_NUMBER to do paging:

WITH OrderedOrders AS
(select SalesOrderID, OrderDate,
Row_Number() OVER (order by OrderDate)as RN
from SalesOrderHeader )
SELECT * from OrderedOrders WHERE RN between 50 and 60

Well… At least, M$ have done something to improve the situation, but not all. It takes me all night to find out the way. I have even thought about switching to MySQL with a very simplier paging syntax, but this is my thesis😛, so I can’t.Thanks to the communities.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31741

Okey, time to continue my work.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: