Paging in SQL Server

Question

Hi,

Please, I'm developing a web application and I have two tables that could be many many records. I need make a query search (stored procedure) that limit the result to 20 records for page.

I think that I must use ROW_NUMBER ou create a temp table, but the fact is that I don´t know why.

Can you help me?

Thanks

Marcos Cruz, São Paulo, Brasil

SELECT     s.SetorAtividadeId                    
          ,s.SetorAtividade                    
          ,rAtividade.RamoAtividadeId                    
          ,rAtividade.RamoAtividade                    
FROM                    
           SetorAtividade As s                    
LEFT OUTER JOIN                    
           RamoAtividade as r                    
ON         s.SetorAtividadeId = r.SetorAtividadeId                    
WHERE                         
          (s.SetorAtividade LIKE @param)                    
ORDER BY                     
           sAtividade.SetorAtividade                    
          ,r.RamoAtividade

Answer 1

0

Also refer method 4 http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx Here is the code

Declare @t table(item varchar(100), price float)             
insert into @t            
select 'item1', 20000 union all            
select 'item1', 20000 union all            
select 'item1', 20700 union all            
select 'item2', 57600 union all            
select 'item2', 80120 union all            
select 'item3', 89760 union all            
select 'item3', 87680 union all            
select 'item4', 87680 union all            
select 'item4', 43220 union all            
select 'item4', 43220             
            
Select item,price from            
(            
select row_number() over(order by item) as row_number, * from @t            
) T            
where row_number between 1 and 5

answered Oct 16 '09 at 04:47 AM

Madhivanan
1.1k ● 1 ● 2 ● 6

I wonder if you want to re-post that here (with a link to your blog too) so that ask.sqlteam has a complete answer? Just a thought ... :)

Oct 16 '09 at 07:06 AM Kristen ♦

I dont understand what you actually meant. Do you think my reply is just a duplicate to other reply? [:)].

Oct 19 '09 at 03:21 AM Madhivanan

Sorry. No, I mean to duplicate the code from your Blog here too - so that there is a complete answer here. I think better for Ask.SQLTeam "completeness", good for Google Spiders, etc.

Oct 19 '09 at 11:10 AM Kristen ♦

Kristen, Now I understand what you meant. Thanks. I will do it [:)]

Oct 20 '09 at 03:13 AM Madhivanan

(comments are locked)

Answer 2

0

You can do paging like this in SQL 2005+

SELECT TOP 40 StudentId, StudentName            
FROM (            
    SELECT ROW_NUMBER() OVER(ORDER BY StudentName ASC) as row,             
    	StudentId, StudentName             
    FROM (            
    	SELECT [Student].[StudentId] AS StudentId, [Student].[StudentName] AS StudentName			            
    	FROM Student Student             
    ) query             
) page             
WHERE page.row > 0             
ORDER BY StudentName ASC

answered Oct 16 '09 at 12:04 AM

craigvn
23 ● 1 ● 1 ● 2

what is the purpose of page.row > 0 ?

Oct 16 '09 at 12:08 AM Squirrel 1

(comments are locked)

Answer 3

0

We talked about paging result sets over here.

answered Oct 16 '09 at 04:47 PM

mrdenny
897 ● 3

(comments are locked)

Paging in SQL Server

Follow this question

Related Questions