SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Server Side Paging using SQL Server 2005
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 01/14/2008 :  14:25:16  Show Profile  Visit graz's Homepage  Reply with Quote
quote:
Originally posted by sql777

wouldn't it be faster by JUST putting the rowNumber and PKID in the CTE, and then doing an inner join on the actual table? (basically keeping the CTE as lean as possible).

Graz?



Hmmm. In order to make the CTE function properly wouldn't it need all the joins and where clauses information? Once you've got that there it's pretty easy to add the column. Do you have an example?

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 01/14/2008 :  14:50:31  Show Profile  Reply with Quote
Say for example, the forums.

You want to grab all the posts for a given forum.

You create your CTE:

MyCTE
(

SELECT postID, Row_Number() (ORDER BY RowID DESC) as RowNumber
From Posts
Where forumID = @forumID
)

SELECT p.*
FROM Posts p
INNER JOIN myCTE cte ON (p.postID = cte.postID)
WHERE rowNumber Between @firstRow AND @lastRow


Since the Posts table has varchar(max) or ntext, and many other columns, keeping the CTE as lean as possible should be faster.

does that make sense?


Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 01/14/2008 :  16:46:07  Show Profile  Visit graz's Homepage  Reply with Quote
It does make sense. Why don't you do a test and post the results? I'm interested to see them.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

rehle
Starting Member

USA
1 Posts

Posted - 01/17/2008 :  09:36:17  Show Profile  Reply with Quote
Here is a twist on the solution provided. This allows me to just write a new view or use a table to get paging results. I have taken this alot further then just the query. I have a few support table and I extended my grid controls, both for windows and the web to include a new property. By setting the custom property the grid gets it entire setup parameters. In the end though I have (1) stored procedure that runs all my grids.

Here is my twist on the query:

CREATE PROCEDURE [dbo].[sp_Grid_Paging_Sorting_Filtering]
(
@pageNum int
,@pageSize int
,@TableName nvarchar(100) --This can be a table or view
,@orderby varchar(max)
,@whereclause varchar(max)
)

AS

declare @lownum nvarchar(10)
declare @highnum nvarchar(10)
declare @sql nvarchar(4000)

set @lownum = convert(nvarchar(10), (@pagesize * (@pagenum - 1)))
set @highnum = convert(nvarchar(10), (@pagesize * @pagenum))

set @sql = 'select * from (
select row_number() over (order by ' + @orderby + ') as rownum
,*
from ' + @TableName + ' ' + @whereclause + '
) A WHERE RowNum > ' + @lownum + ' AND rownum <= ' + @highnum

-- Execute the SQL query
EXEC sp_executesql @sql
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 04/22/2008 :  12:05:34  Show Profile  Reply with Quote
quote:
Originally posted by rehle

Here is a twist on the solution provided. This allows me to just write a new view or use a table to get paging results. I have taken this alot further then just the query. I have a few support table and I extended my grid controls, both for windows and the web to include a new property. By setting the custom property the grid gets it entire setup parameters. In the end though I have (1) stored procedure that runs all my grids.

Here is my twist on the query:

CREATE PROCEDURE [dbo].[sp_Grid_Paging_Sorting_Filtering]
(
@pageNum int
,@pageSize int
,@TableName nvarchar(100) --This can be a table or view
,@orderby varchar(max)
,@whereclause varchar(max)
)

AS

declare @lownum nvarchar(10)
declare @highnum nvarchar(10)
declare @sql nvarchar(4000)

set @lownum = convert(nvarchar(10), (@pagesize * (@pagenum - 1)))
set @highnum = convert(nvarchar(10), (@pagesize * @pagenum))

set @sql = 'select * from (
select row_number() over (order by ' + @orderby + ') as rownum
,*
from ' + @TableName + ' ' + @whereclause + '
) A WHERE RowNum > ' + @lownum + ' AND rownum <= ' + @highnum

-- Execute the SQL query
EXEC sp_executesql @sql




I don't like this solution. Why not copy/paste and have a SProc for each table. I don't think dynamic SQL is a good idea unless it's a last resort.
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 04/22/2008 :  12:06:05  Show Profile  Reply with Quote
Is the concern here about GUID's valid?
http://blog.vermorel.com/?p=83
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 04/22/2008 :  12:08:24  Show Profile  Reply with Quote
Introducing Paging will involve me as the DBA to work with the developers. Introducing Partitioning will not. And it seems to me I'll get a much bigger performance benefit from Partitioning. So it seems wiser to do that before looking at Server-side Partitioning. Would you agree?
Go to Top of Page

RobKraft
Starting Member

USA
1 Posts

Posted - 06/25/2008 :  11:27:40  Show Profile  Visit RobKraft's Homepage  Reply with Quote
In reply to SQL777 and Graz's exchange about performance, my tests show that joining the table outside of the WITH blick is about 10 times faster. So, assuming M_Name is unique in this example, you should revise it as follows:
DECLARE	@PageSize INT,
	@PageNumber INT,
	@FirstRow INT,
	@LastRow INT

SELECT	@PageSize = 20,
	@PageNumber = 3

SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
	@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

WITH Members  AS
(
	SELECT	M_NAME,
	ROW_NUMBER() OVER (ORDER BY M_NAME DESC) AS RowNumber
	FROM	dbo.FORUM_MEMBERS
)
SELECT	RowNumber, fm.M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY
FROM	Members m inner join FORUM_MEMBERS fm on m.M_NAME = fm.M_NAME
WHERE	RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY RowNumber ASC;


In reply to rehle and coolerbob, I'm doing something very similar to what rehle does because we let the users choose which columns to display, how to sort, and which of 600 tables they want to query against. There is no way to do this without dynamic code. But we do make sure that SQL Injections won't happen!
Go to Top of Page

genxt
Starting Member

India
2 Posts

Posted - 06/26/2011 :  12:39:31  Show Profile  Reply with Quote
I've found this link very useful.
This post has some paging queries and a C# function which will convert any query into a paging query by passing start value and rows per page value as arguments.
Go to Top of Page

genxt
Starting Member

India
2 Posts

Posted - 06/26/2011 :  12:40:47  Show Profile  Reply with Quote
Sorry guys forgot to paste the link.
http://www.arunraj.co.in/index.php?option=com_content&view=article&id=2:paging-query&catid=3:aspnet&Itemid=8
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/30/2011 :  11:17:48  Show Profile  Reply with Quote
Hey Bill..just curious...why id there a RowNumber2?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.48 seconds. Powered By: Snitz Forums 2000