Creating Effecient Paging with T-SQL and the IDENT

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

SQL Server Development (2000)

Creating Effecient Paging with T-SQL and the IDENT

Author

Topic

princeofegypt
Starting Member

2 Posts

Posted - 2004-06-07 : 08:48:58

Now we all had the problem of creating a grid with next and back buttons , as it retreieves all the data from the server and filters it on the client site .That is a poor technique if you have a huge table ,few users will hang your machine and result in memory consumption

SQL server lacks a way to limit the number of rows returned to the client in ranges ,and you have to write a very complex query to achieve this result ,like getting rows from 5 to 10 ,and you will depend on a primary key for sure .Lets first discuss how MySQL and Oracle solves this problem ...

Mysql has a very pretty code to solve this problem you can say :

SELECT * FROM foo LIMIT 5,10

this will retrieve the rows starting row number 5 till row number 15 .Very pretty code cause u just drop this { LIMIT N,M } at the end of your most complex query and you are done .

Oracle has a similar approach ,it has a psuedoColumn always availiable to you when you issue a query , this column have a name of ROWNUM .
if you do something like this
SELECT ROWNUM ,* FROM foo you will get

ROWNUM COl1 Col2
1 data data
2 data data
3 data data

Now to page just say WHERE ROWNUM BETWEEN n AND M
Pretty clean code just like the mysql code

Now back to my only love SQLserver ,which is the best engine i worked on despite this issue ,there is no direct way to return a range ,you can only return TOP ,but no range

Here is a little piece of code that you can compile as a stored procedure

SET ROWCOUNT 10 -- set this to your desired page size ,this is for optimization ,the between clause will be enough

select IDENTITY(int, 1,1) as 'ROWNUM' ,* into #temptable from foo
SELECT * from #temptable will yield this

ROWNUM COl1 Col2
1 data data
2 data data
3 data data

which is similar to the ORACLE approach ,and the code will be even compatible with ORACLE as i can say
SELECT * FROM #temptable where ROWNUM between
N and M

your code will be easy to read and understand
Amr Salah

Prince Of Egypt

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-07 : 08:56:30

Prince,

There are numerous articles on this subject:

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

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

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources