Custom Paging

Im in the process of trying to teach myself SqlServer, comming from Oracle. How the heck do I get the equivlent of %ROWNUM pseudo-column in SqlServer? Top just isn't doing it for me.

Oracle Example wrote:

Select * from foo where foo%ROWNUM > 10 and foo%ROWNUM <20;

There is no direct equivalent in SQL Server 2000. There is in SQLServer 2005, but that's another story. To mimic it you have toselect into a temp table with an autonumber column.
Here's an example for Northwind (see thefull article):

CREATE PROCEDURE northwind_OrdersPaged
(
 @.PageIndex int,
 @.PageSize int
)
AS
BEGIN
DECLARE @.PageLowerBound int
DECLARE @.PageUpperBound int
DECLARE @.RowsToReturn int
-- First set the rowcount
SET @.RowsToReturn = @.PageSize * (@.PageIndex + 1)
SET ROWCOUNT @.RowsToReturn
-- Set the page bounds
SET @.PageLowerBound = @.PageSize * @.PageIndex
SET @.PageUpperBound = @.PageLowerBound + @.PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
 IndexId int IDENTITY (1, 1) NOT NULL,
 OrderID int
)
-- Insert into the temp table
INSERT INTO #PageIndex (OrderID)
SELECT
 OrderID
FROM
 Orders
ORDER BY
 OrderID DESC
-- Return total count
SELECT COUNT(OrderID) FROM Orders
-- Return paged results
SELECT
 O.*
FROM
 Orders O,
 #PageIndex PageIndex
WHERE
 O.OrderID = PageIndex.OrderID AND
 PageIndex.IndexID > @.PageLowerBound AND
 PageIndex.IndexID < @.PageUpperBound
ORDER BY
 PageIndex.IndexID
END

|||Nurgle! That's even worse than what you have to do in Oracle to get ROWNUM to work! Now, lets say for a moment that im writing against SqlServer 2k5 april CTP. How would i go about emulating it?|||

Try this link. Hope this helps.

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=633&lngWId=5

|||

Now, lets say for a moment that im writing against SqlServer 2k5 april CTP. How would i go about emulating it?

ROW_NUMBER.

|||

ndinakar wrote:

Now, lets say for a moment that im writing against SqlServer 2k5 april CTP. How would i go about emulating it?

ROW_NUMBER.

would you mind elaborating on how to use this?

|||

Check out this article:http://www.codeproject.com/aspnet/PagingLarge.asp. It handles various paging methods you can use. Aso it has benchmarking results given for each method and comparisons between performance of different methods.

|||Check out this tutorial, which also covers the new RANK function as well:
http://www.sqljunkies.com/HowTo/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk
|||Thats a good article Darrel. Most of the commonly used commands summed up in one article. Thanks for the link. I was going to point to the Books on line.|||I'm really impressed by the SQL Server BOL in general. For somereason I think it does a better job than the MSDN library. Idon't know if it's the language (SQL vs. C# or VB), me, or what, but itjust works for me.
|||

Microsoft was selling it $100 but was persuaded by Boolean searches to give it away. This was before the Ken Henderson books and it saved the MCDBA program from the brink of disaster. Who came up with the idea to sell product documentation I don't know because product documentation is usually free but resource kit is not free. But in 2003 when told about developers need for SQL Server they listened and dropped the price of the developer's edition to from $450.00 to $49 or less. So every department leave files for Boolean searches you just have to look for them on any Microsoft site.

|||

DarrellNorton wrote:

I'm really impressed by the SQL Server BOL in general. For some reason I think it does a better job than the MSDN library. I don't know if it's the language (SQL vs. C# or VB), me, or what, but it just works for me.

DOH! I knew there was something i was forgetting to register for and to think all this time i thought it was the motorcycle safety course!|||Try this link. Hope this helps.
http://www.richardxin.com/SQLPaging.aspx