Well, I have to say I was extremely disappointed with this article. Paging is such a core operation within the realm of web development that to require a temp table, a couple of functions, and still two or three other queries to accomplish what you need is a bit gross.

So, here's my solution which can be wrapped in a single stored procedure:

declare @PageNum int;
declare @RecCount int;

set @PageNum = 2;
set @RecCount = 5;

WITH RowPages(RowNumber, OrderID, OrderDate, CustomerID, EmployeeID, ShipVia)
AS (
SELECT TOP( @PageNum * @RecCount )
RowNumber = ROW_NUMBER() OVER (ORDER BY OrderDate, OrderID),
OrderID, OrderDate, CustomerID, EmployeeID, ShipVia
FROM Orders
)

SELECT *
FROM RowPages
WHERE RowNumber > ((@PageNum - 1) * @RecCount);


------------
Chris Lively
www.livelyconsulting.com

Anonymous User 6/3/2005 9:03:30 AM

... cont ...

Comment: “You could use ROWCOUNT instead of TOP n. Is much faster and gives you a lot of new possibilities.”

Reply: actually, TOP has better optimization potential than SET ROWCOUNT since with TOP the optimizer can more easily “sniff” the input value. Usually, you either see similar plans, or better plans for TOP, and not the other way around. It’s true that up until and including SQL Server 2000 SET ROWCOUNT had advantages like supporting a variable input and modifications. But both limitations of TOP are gone in SQL Server 2005. Yukon will support an expression as an input to TOP, and will allow TOP in all DML statements (SELECT, INSERT, UPDATE and DELETE). In fact, SET ROWCOUNT will probably enter deprecation process in the version following SQL Server 2005. So I’m not sure I’d recommend programmers to use SET ROWCOUNT knowing that it’s already known that it will not be supported in the future. I’d recommend to stick to TOP.

Comment: “Useful article. However I'm disappointed. Why not support a MySQL style LIMIT clause. Paging is a basic operation. Why the need for a hack? “

Reply: Well, I wouldn’t call the enhancements in SQL Server 2005 a hack. I do strongly believe that the introduction of analytical ranking functions, TOP enhancements, APPLY, etc. are going to allow significantly more flexible and better performing paging solutions.

Comment: “if microsoft dont apply rownum and rowcount in sqlserver 2006 i will not use sqlserver ever...that's it.”

Reply: Next version of SQL Server is 2005, and yes, ROW_NUMBER() is in.

Comment: “This is an interesting article that I'm still digesting. One query: why in the first SELECT do you have 'OrderID+0 AS OrderID'? This is obviously a SQL Server idiom that I'm missing. Anyone care to throw some light on it? Thanks Michael “

Reply: Yes, it’s a tricky one. :-)
The thing is that OrderID is an IDENTITY column in the source table, and if you don’t want the target column of the SELECT INTO statement to be an IDENTITY column, all you need to do is some manipulation of the base column. And I performed manipulation that doesn’t change the values, rather only gets rid of the IDENTITY property.

Comment: “I find it unbelievable that sql server 2005 won't support offset and count clauses in a sql select statement as mysql, postgresql, and other databases do...”

Reply: I think that the enhancements in SQL Server 2005 are going to allow better paging solutions, and good performing ones objectively. But if you feel strongly about a feature that you feel that is missing, you can send your request to sqlwish@microsoft.com. Every request in this mailbox is read by SQL PMs and is seriously considered. The more requests there are for a feature, the better the chances are that it would appear in one of the future versions.

Again, thanks for all your comments,
Itzik Ben-Gan


BG_SQL 5/17/2005 9:22:18 PM

Thanks to all readers who posted comments. I can understand the interest in the subject since so many production applications implement paging.
Before I address the comments, I’d like to point out a very important knowledge base article that was revised after I opened a case with Microsoft to clarify the confusion surrounding IDENTITY issues. I strongly recommend going over it as it has many implications related to paging in SQL Server 2000.
Here’s the link to the KB article (273586) The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause:

http://support.microsoft.com/default.aspx?scid=kb;en-us;273586

Let me address your comments:

Comment: “Well its better than SQL 2000. But still i seriously think MS should make ROWNUM and ROWID available for all selects by default. Oracle gives u easier way to do paging, SQL needs few steps.”

Reply: ROW_NUMBER() in SQL Server 2005 is available for all SELECTs if you request it. :-)
As for ROWID, that’s arguable, since the whole concept is not relational. Revealing something that is very tightly related to the physical representation of the data, as opposed to the logical layer that should be exposed to the user. ROW_NUMBER() is ANSI, and is implemented in the product in an extremely efficient manner (using one scan of the data).

... more to follow ...

Itzik Ben-Gan

BG_SQL 5/17/2005 9:22:01 PM

I find it unbelievable that sql server 2005 won't support offset and count clauses in a sql select statement as mysql, postgresql, and other databases do...

Anonymous User 5/6/2005 8:54:00 AM

This is an interesting article that I'm still digesting. One query: why in the first SELECT do you have 'OrderID+0 AS OrderID'? This is obviously a SQL Server idiom that I'm missing. Anyone care to throw some light on it?
Thanks
Michael


Michael4/21/2005 10:51:58 AM

if microsoft dont apply rownum and rowcount in sqlserver 2006 i will not use sqlserver ever...that's it.

Anonymous User 4/1/2005 5:39:33 AM

Useful article. However I'm disappointed. Why not support a MySQL style LIMIT clause. Paging is a basic operation. Why the need for a hack?

Anonymous User 3/29/2005 1:18:11 AM

You could use ROWCOUNT instead of TOP n. Is much faster and gives you a lot of new possibilities.

Anonymous User 3/17/2005 2:43:56 AM

Well its better than SQL 2000. But still i seriously think MS should make ROWNUM and ROWID available for all selects by default.

Oracle gives u easier way to do paging, SQL needs few steps.

Anonymous User 1/10/2005 10:19:05 PM