SqlServer 2005 paging: there IS a generic wrapper query possible

links for 2007-06-07 » mhinze.com

links for 2007-06-07 » mhinze.com — Thu, 07 Jun 2007 15:27:03 GMT

Pingback from links for 2007-06-07 » mhinze.com

re: SqlServer 2005 paging: there IS a generic wrapper query possible

Sean — Tue, 05 Jun 2007 21:12:25 GMT

Careful, working around TOP 100 PERCENT might be going around a technical limitation, better hit that speed dial button to MS EULA Central

re: SqlServer 2005 paging: there IS a generic wrapper query possible

FransBouma — Tue, 05 Jun 2007 16:15:53 GMT

Oren, indeed! that's indeed a bummer I didn't think of. Thanks for the heads up.

Hmm... Indeed something to think of when writing paging queries.. :/

(the layout of your reply is messed up, sorry for that, the approval api of CS is still a bit shaky in some areas apparently, so it then removes linebreaks)

re: SqlServer 2005 paging: there IS a generic wrapper query possible

Ayende Rahien — Tue, 05 Jun 2007 15:47:19 GMT

Frans, This works:
select id id, anotherid id from customers
This gives syntax error:
WITH __actualSet AS
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt
FROM
(
select id id, anotherid id from customers
) AS _tmpSet
)
SELECT * FROM __actualSet
WHERE [__rowcnt] > 0
AND [__rowcnt] <= 16
ORDER BY [__rowcnt] ASC

The original query is WTF, I agree, but I run into this issue yesterday

re: SqlServer 2005 paging: there IS a generic wrapper query possible

FransBouma — Tue, 05 Jun 2007 13:38:13 GMT

Yes the TOP 100 PERCENT is indeed not working! :) I found that out myself right after posting this and tried a DESC sort instead of an ASC sort.. it didn't give the results I was after. Very odd. (So I removed that remark ;))

The ROW_NUMBER trick inside the query is indeed an option, but that is more cumbersome to create, and is a bit less of a 'wrapper' for a query to page.

re: SqlServer 2005 paging: there IS a generic wrapper query possible

David Markle — Tue, 05 Jun 2007 13:22:12 GMT

Frans: Great article. I just did something quite close to this myself at work for a coworker. I wonder how well it scales to large numbers of rows. The only thing that you should be careful of is that TOP 100 PERCENT doesn't really work in SQL 2005. Take a look at my (non-inflammatory) blog entry: http://executioniseverything.blogspot.com/2007_01_01_archive.html Scary new behavior, huh!? Actually, instead of TOP, you can use ROW_NUMBER() with an ORDER BY once again, inside your CTE to produce a properly ordered set -- that'll do the trick.