February 4, 2009 at 1:40 am
I'm pretty sure it should work with any orderable criteria.
The real question I have is whether this is stable behaviour of rowcount and select @variable, or if this is leveraging an undocumented feature that may be deprecated/changed without notice.
February 4, 2009 at 7:44 am
I dont see how it can. You have to have something to return in the first variable to use as the beginning of your range, and unless its already in the table sequentially you would have to create one somehow. This does not seem very efficient, even redundant since you would not need this first query and variable at all because I already know the first row index I need. I could just use a BETWEEN to get the page I need, or a TOP(N) would do just as good (see second example).
SET ROWCOUNT @StartRowIndex
SELECT
@RowNumber = ROW_NUMBER() OVER ([Column1, Column2, ...])
FROM
SomeTable
ORDER BY
[Column1, Column2, ...]
SET ROWCOUNT @MaximumRows
SELECT
[Column1, Column2, ...]
FROM
SomeTable A
INNER JOIN
(SELECT [IDColumn], ROW_COUNT() OVER ([Column1, Column2, ...]) AS RowNumber FROM SameTable)
B ON A.[IDColumn] = B.[IDColumn]
WHERE
B.RowNumber >= @RowNumber
ORDER BY
B.RowNumber
--- Single Query Method ---------------------------
SELECT TOP (@MaximumRows)
[Column1, Column2, ...]
FROM
SomeTable A
INNER JOIN
(SELECT [IDColumn], ROW_COUNT() OVER ([Column1, Column2, ...]) AS RowNumber FROM SameTable)
B ON A.[IDColumn] = B.[IDColumn]
WHERE
B.RowNumber >= @StartRowIndex
ORDER BY
B.RowNumber
Now I would be impressed if anyone could find a way to get the total records available without having to have a second query to get it. That would be nice.
February 4, 2009 at 8:03 am
You can use COUNT(*) OVER ('') to get the total count.
For example:
SELECT
name,
count(*) over (partition by '') AS TotalCount
FROM sys.tables
.
February 4, 2009 at 9:02 am
The point is that you DONT need to count anything.
You need something vaguely sequential though, but I don't see how you could ever page anything if you didn't have some orderable field.
I have done some more testing, and it's actually not that great on a performance side - great on speed, but read heavy.
February 4, 2009 at 9:20 am
JRodman (2/4/2009)
I dont see how it can. You have to have something to return in the first variable to use as the beginning of your range, and unless its already in the table sequentially you would have to create one somehow. This does not seem very efficient, even redundant since you would not need this first query and variable at all because I already know the first row index I need. I could just use a BETWEEN to get the page I need, or a TOP(N) would do just as good (see second example).
I believe the point of the article you are discussing was that it works on sql 2000. We didn't have use of top(@var) syntax in 2000, so I believe the reason for the approach taken was to work around this limitation.
It does work with any ordered column, you don't need a sequential int field. E.g.
create table #test (EmpName varchar(50) primary key clustered)
declare @i int
set @i = 0
while (@i < 1000)
begin
insert #test values ('BogusName' + cast(@i as varchar))
set @i = @i + 1
end
go
--execution params
DECLARE
@startRowIndex int,
@maximumRows int
SELECT
@startRowIndex = 25,
@maximumRows = 7
--worker vars
DECLARE @firstName varchar(50)
SET ROWCOUNT @startRowIndex
SELECT @firstName = EmpName FROM #test ORDER BY EmpName
SET ROWCOUNT @maximumRows
SELECT EmpName
FROM #test
WHERE EmpName >= @firstName
ORDER BY EmpName
February 4, 2009 at 9:21 am
I wish it was as simple as that. I cant remember the last time I was able to use a column in a table to sequentially order results. In my case most of the time the ordering is dynamic, such as ordering by a few different columns both ascending and descending and allowing the user to choose such as clicking on a grid column header. In that case you cannot use a column in the table because you have no sequential key to use. So far I am stuck using ROW_COUNT() to give me that key to use for paging. It sure beats using a temp table with an identity column before SQL 2005 came out... And I still need the total number of records so I can calculate the number of available pages there are. I end up having a second query to get COUNT(*) for that calculation. I was hoping someone could come up with a slick way to do that in the main select statement without a second query, but I haven't found a way yet since you are only pulling the page you want back.
February 4, 2009 at 9:33 am
JRodman (2/4/2009)
I wish it was as simple as that. I cant remember the last time I was able to use a column in a table to sequentially order results. In my case most of the time the ordering is dynamic, such as ordering by a few different columns both ascending and descending and allowing the user to choose such as clicking on a grid column header. In that case you cannot use a column in the table because you have no sequential key to use. So far I am stuck using ROW_COUNT() to give me that key to use for paging. It sure beats using a temp table with an identity column before SQL 2005 came out... And I still need the total number of records so I can calculate the number of available pages there are. I end up having a second query to get COUNT(*) for that calculation. I was hoping someone could come up with a slick way to do that in the main select statement without a second query, but I haven't found a way yet since you are only pulling the page you want back.
I don't love the solution, but I have used this
create table #test (testId INT primary key clustered)
;WITH digits AS (
SELECT 0 as Number
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
)
insert #test
SELECT
(hThousands.Number * 100000)
+ (tThousands.Number * 10000)
+ (thousands.Number * 1000)
+ (hundreds.Number * 100)
+ (tens.Number * 10)
+ ones.Number AS Number
FROM digits AS ones
CROSS JOIN digits AS tens
CROSS JOIN digits AS hundreds
CROSS JOIN digits AS thousands
CROSS JOIN digits AS tThousands
CROSS JOIN digits AS hThousands
go
;with cte as
(
select
testId
,rowNum=row_number() over (order by testId)
from #test
)
select
testId
,rowCount = (select max(rowNum) from cte)
from cte
where rowNum between 10 and 20
go
The main drawbacks are (1)each time you reference the CTE it does the work of row numbering again, (2) to get the max(rowNum) it has to actually calculate the rowNum for all rows of the CTE row_number() definition, and (3) you are duplicating the rowCount on every row you return (wasted bytes on the wire and in buffers).
The only real positive is you don't have to copy and paste your query to get the rowcount in a second query. I have gone this route before as well and inevitably fix one of the two query instances down the road but not the other, so the row count is no longer accurate for the filter criteria 🙁
February 4, 2009 at 9:47 am
I have found that getting a count(*) on most of the things I would page is simply nonsensical from a performance perspective.
Usually I have put in a system to get the first and last pages, the next and previous pages. The code then passes in and out the primary key for the first record to start at for the page, and goes on from there. By returning 1 record extra you know if there's another page. The app knows if it asked for the next page, so it knows if there's a page before. By doing a straight exam on the pk, its lightweight and fast.
I have run some tests on a small table with a few hundred million rows, and the results were irritating... this new way runs a large 33k reads to get the page start, and 16 reads to get the page data. My old way only does the 16 reads... harder to manage, less nice to use perhaps, but loads better on the scalability front 🙂
February 4, 2009 at 9:56 am
I can see how that probably would be a descent way to cut corners if you absolutely had to for performance sake. But in most cases my requirements dictate showing the record count somewhere on the screen, so I have to get it anyway.
February 13, 2009 at 5:23 pm
I'm new to this concepts,
how can I get the Rownumber of particular primary key to go to this page?
Regards Davor
March 28, 2009 at 5:49 am
I don't know if this is off topic but will try:
I have successfully implemented server side paging through a stored procedure using a CTE like it was explained in your article.
now it comes my question:
is there a way to retrieve the page number where I can find a given record ?
example: a new record is inserted in a table ordered by name and the name in this new record is "Smith"
after submitting the new record my application returns to the list of the customers showing all the names starting with "Alfreds ..."
It would be much more useful to directly show the page where the new record was added.
For doing this I need to know what page number I have to retrieve from the database knowing the ID of the new inserted record.
I have googled around a while without finding nothing but, maybe, I searched in the wrong direction.
I would be very impressed if someone could help me solve this issue
Best regards
March 28, 2009 at 7:08 am
Hello Franco,
I implemented the algorithm in my project and it works like a baby with 1.5 millions of records.
Thnx goes to Jacob.
In this code I'm using table called UFAGL and the SQL Server is SQL SERVER 2008. I think that it will work in 2005 also.
If You have any question post it here, so that anyone can benefit from it.
If you having WHERE or JOINS you need to implement them.
Regards, Davor Geci
-- big thanks goes to Jacob Sebastian, creator of this algorithm
-- regards Davor Geci
DECLARE @PageNumber AS BIGINT = 1;
DECLARE @Pages AS FLOAT = 0;
DECLARE @RowsPerPage AS FLOAT = 100;
DECLARE @RecIDNum AS BIGINT = 0;
DECLARE @Records AS FLOAT = 0;
DECLARE @FindThisRecord AS FLOAT = 98111220929667;
--**************************** PART 1 *****************************
-- order records by Order By
-- if you have WHERE or JOINS put also here for accurate results
-- here you need to retrive only RecID and field for searching your record
WITH tmp1 AS
(SELECT ROW_NUMBER () OVER (ORDER BY UFAGL.BRRAC DESC ) AS RecID,
UFAGL.BRRAC
FROM UFAGL)
-- get the position of my record and total number of records
SELECT @RecIDNum = RecId,
@Records = (select max(RecID) from tmp1)
FROM tmp1
WHERE BRRAC = @FindThisRecord -- if you don't have record to find skip this WHERE
SET @PageNumber = ceiling (@RecIDNum/@RowsPerPage) ; -- get the page with my record, if you don't have record to find skip this, or you can skip whole PART 1 by replacing it with @Pages = Count()
SET @Pages = ceiling (@Records/@RowsPerPage) ; -- get number of total pages
--**************************** PART 2 *****************************
-- this is the acctual sql for getting my records ordered by order by field
-- if you have WHERE or JOINS put also here for accurate results
WITH tmp AS
(SELECT ROW_NUMBER () OVER (ORDER BY UFAGL.BRRAC DESC ) AS RecID ,
[UFAGL].[STATUS] AS [St] ,
[UFAGL].[SKLAD] AS [Sklad],
[UFAGL].[TIP] AS [Knj]
FROM UFAGL)
-- return only needed page of records from tmp
SELECT [St] ,
[Sklad],
[Knj]
FROM tmp
WHERE RecID BETWEEN
(((@PageNumber-1) * @RowsPerPage) + 1)
AND
(@PageNumber * @RowsPerPage)
--**************************** PART 3 *****************************
--Return values:
SELECT @Pages -- return number of pages
SELECT @PageNumber -- return actual page
SELECT @Records -- return number of total records
March 28, 2009 at 8:23 am
Hi Davor,
I thank you very much for your quick replay.
I tried this algorithm with the Query builder and it worked like a charm.
Very brilliant.
March 28, 2009 at 8:42 am
Glad to help,
I have edited the post several times.
I have optimized it a little bit, so check if you have the last version.
Maybe Jacob wants to put this in Part 2 also.
(Its time for Part 2 🙂 )
Jacob??
Regards, Davor Geci
March 28, 2009 at 11:46 am
I wanted to come up with a PART 2 which focuses on using the results in an ASP.NET page (display paging information etc). Well, now I think there are enough reasons to have a revised version of the code (including all the knowledge shared in this discussion from various sql experts and the .net side of it)
.
Viewing 15 posts - 46 through 60 (of 61 total)
You must be logged in to reply to this topic. Login to reply