Best way to make a table paging in MS SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Best way to make a table paging in MS SQL 2000

Author

Topic

overthetop
Starting Member

18 Posts

Posted - 2006-08-09 : 06:39:37

Hi

what is the best way to page a table in sql server 2000. I think that using temp tables is out of the question. So can you tell me what is the right way and if someone can give me link to read more :)

10x

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-08-09 : 07:42:11

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-09 : 10:14:26

Also refer
http://www.aspfaq.com/show.asp?id=2120

Madhivanan

Failing to plan is Planning to fail

overthetop
Starting Member

18 Posts

Posted - 2006-08-10 : 09:09:52

quote:
Originally posted by RyanRandall

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

yes but if you don't have an identity column? For example if you have a table [id], [name] ([id] is guid) how can you do the paging?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-08-10 : 10:27:26

quote:
Originally posted by overthetop

quote:
Originally posted by RyanRandall

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
yes but if you don't have an identity column? For example if you have a table [id], [name] ([id] is guid) how can you do the paging?

The example in the article uses an identity column, but I don't think you need one to use the method suggested in the article. I think you just need something unique to order by - which may be one or many columns.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

overthetop
Starting Member

18 Posts

Posted - 2006-08-11 : 15:04:14

I'm sorry it works fine

declare @startName nvarchar(50)
declare @startGuid uniqueidentifier

declare @rowIndex int
declare @pageSize int

set @rowIndex = 3
set @pageSize = 4

set rowcount @rowIndex
select @startGuid = [guid],
@startName = [name]

from Table_2
order by [name], [guid]

set rowcount @pageSize
select [guid],
[name]
from Table_2
where [name] > @startName or ([guid] > @startGuid and [name] = @startName)
order by [name], [guid]

set rowcount 0

but I can't understand why it works. I am sorry if this looks stupied but I am not so good in t-sql. What is the meaning of this

...where [name] > @startName or ([guid] > @startGuid and [name] = @startName)...

what this [name] > @startName means? What this compares? Please someone can explain me the logic. HELP

You are right this time :) "Solutions are easy. Understanding the problem, now, that's the hard part."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources