?
Solved

Pagination query with MSSQL Server

Posted on 2005-03-03
13
Medium Priority
?
2,340 Views
Last Modified: 2012-06-27
Hii,

I am using the following for doing the pagination in Oracle.

SELECT * FROM (SELECT tt.*, ROWNUM ROWNO   FROM (
 SELECT
 blah...blah..
 FROM table
 WHERE some=some ) tt  WHERE ROWNUM <= 20)  WHERE ROWNO >0

How can i emulate it for MS SQL Server?

Any ideas please.

Thanks
aks
0
Comment
Question by:aks143
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 400 total points
ID: 13448944
Hi aks143,


The best I have for paging with MS SQL is ...

in MySQL :
SELECT * FROM test order by idtest LIMIT 3000, 20

in MSSQL :
select * from (select top 20 * from (select top 3020 * from test order by
idtest) as t1 order by idtest DESC) as t2 order by idtest


This is from my own pager notes. MySQL supports paging, MSSQL does not. I have the comment "You can also use SET ROWCOUNT before the main select". Maybe useful.


set rowcount $limit
select * from Table1 where pk in(
select top $total pk from Table1 order by pk desc )order by pk



Table1: name of the table
PK : Primary Key of Table1
$limit : The number of records you need each time $total : Total number of records in each time

The only thing you need to do is to get the total number of records (select
count(*)) for the first $total value then $n =$total/$limit

//Forloading next page
$total = $total - $limit
//then run the sql statement with new $totalvalue

//For loading page 2
$total = $total - ($limit)*2

//For loading page n
$total = $total - ($limit)*n


1-You can put this code in a stored procedure and pass $limit and $total
values when you calling the stored procedure or you can use it in your php
code

2-You can change the SQL statement to work with your search-query results
instead of Table1


Regards,

Richard Quadling.
0
 

Author Comment

by:aks143
ID: 13449104
Hii Richard,

thanks for ur quick reply. I am using java and jsp technology and the pagination is done on sql query level. In the mentioned example

in MSSQL :
select * from (select top 20 * from (select top 3020 * from test order by
idtest) as t1 order by idtest DESC) as t2 order by idtest

what is "3020" and if it is the total count, it means for each execution of the query i need to have the total count. Is it?

Please reply.
aks
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 13449221
No.

This is select entries 3000 to 3020.

If you get no results you are beyond the limit.

Or is it 3020 to 3040.

Not too sure to be honest.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 40

Expert Comment

by:Richard Quadling
ID: 13449280
select top 20 * from (select top 3020 * from test order by idtest DESC) order by idtest

Might be better.

If you have the following variables ...

RowsPerPage = 20
PageToShow = 150

Then ...

selelct top RowsPerPage from (select top ((PageToShow + 1) * RowsPerPage) * from test t1 order by t1.idtest desc) as t2 order by t2.idtest

Should do the trick. I've not tested this, and you will need to build this up manually. RowsPerPage and PageToShow will need to be inserted manually.

RIchard.

0
 
LVL 12

Assisted Solution

by:patrikt
patrikt earned 200 total points
ID: 13449349
RQuadlings double-order solution will work but it can be slow with multiple users and queries.

Pagination is not a simple probleme. First you have to answer question what to do with new/deleted reccords between page queries.
Answers can be:
- Create temporary table for each user at start of first page query. User will have all reccords on correct pages but has to call for refresh if he wants to see new data.
- Use on-the-fly pages (as it is in RQuadlinks) but user can loose-skeep data if it is inserted or deleted from pages he already seen. Or can see different results when refreshing same page.

Generaly it is good to support pagination by some generated row number as this:

select tmp.* from (select *,identity(int,1,1) as rowno from Test) tmp where tmp.rowno between 20 and 30

0
 
LVL 12

Expert Comment

by:patrikt
ID: 13449360
I forget order there so again:
select tmp.* from (select *,identity(int,1,1) as rowno from Test order by Test.date) tmp where tmp.rowno between 20 and 30
0
 
LVL 12

Expert Comment

by:patrikt
ID: 13449419
This is tested example. I write so fast without testing, it is not good :)

use Northwind
select *,identity(int,1,1) as rowno into #tmp from customers
select * from  #tmp where rowno between 1 and 5
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 13449479
Oh yes. Pagination is a REAL pain. Snapshot of view is very much the best way. But on a table with millions of rows and a user wants to arbitrarily view the data, you are going to have to accept the fact that the data changes.

0
 

Author Comment

by:aks143
ID: 13449940
hi,

thanks for the replies. Well Richard, ur case might be useful. But do i need to care much on order by clause. Actually i want to have a generalised query. If the paging result is dependent on order by clause..???

patrikt, i would like to use the identity(int,1,1) in my mentioned query for oracle as

SELECT * FROM (SELECT tt.*, identity(int,1,1) AS ROWNO   FROM (
 SELECT
 blah...blah..
 FROM table
 WHERE some=some ) tt  WHERE ROWNUM <= 20)  WHERE ROWNO >0

But it seems not working.

P.S: i have almost nill experience with MSSQL Server. So please bear, if i sound wierd.

thanks
aks
0
 
LVL 12

Expert Comment

by:patrikt
ID: 13450177
My first example is not working on MSSQL. You have to use second one which is storing intermediate result into temporary table. It is work for stored procedure.
If you giveme your requests, I'll try to send you something to start with.
0
 
LVL 12

Expert Comment

by:patrikt
ID: 13450263
Something general. As school task :)

CREATE PROCEDURE PageN (@pagesize int,@pageno int,@ordercol varchar(30),@table varchar(30))
AS
declare @sql varchar(1000)
declare @n int
set @n=(@pagesize+1)*@pageno
set @sql='select top '+cast(@n as varchar)+' *,identity(int,1,1) as rowno into #tmp from '+@table+' order by '+@ordercol
sp_execuresql @sql
select * from #tmp where rowno between @pagesize*(@pageno-1) and @pagesize*@pageno

0
 
LVL 12

Expert Comment

by:patrikt
ID: 13450324
As usual it has errors so there is debuged one:

ALTER PROCEDURE PageN (@pagesize int,@pageno int,@ordercol varchar(30),@table varchar(30))
AS
declare @sql nvarchar(1000)
declare @n int
set @n=(@pagesize+1)*@pageno
set @sql='select top '+cast(@n as varchar)+' *,identity(int,0,1) as rowno into #tmp from '+@table+' order by '+@ordercol
EXEC sp_executesql @sql
select * from #tmp where rowno between 1+@pagesize*(@pageno-1) and @pagesize*@pageno
order by rowno
0
 

Author Comment

by:aks143
ID: 13485170
hii,

sorry i m bit late to respond. Patrikt, it is no school task. I generalise the handling by using the scrollable result set in java. MSSQL Server is really dumb in comparison with Oracle.

thanks for you all.
keep up the good work.
aks
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question