Attention: We are retiring the ASP.NET Community Blogs. Learn more >

Paging in SQL Server

Frans rants about paging support in SQL Server. I obviously agree with him.

The good thing is that given that MS is working in a couple of O/R mappings products, they are probably facing the same challenges we face. As that the Entity Framework is built by the SQL Server team, I won't be surprised if Katmai  has better support for paging ;).


4 Comments

  • Let's hope they sent the scientists on a holiday break when the rest of the team hammers down the API details ;)

  • We also did with DeKlarit, and I'm sure Frans also did it, but nevertheless, it's a pain.

    It can get trickier when you need to return a page of header and details (like the second page of orders with its order lines, in 2 sql sentences one for the header and other for the lines), which DeKlarit also supports.

  • That paging query is rather easy :) With a prefetch path you fetch the header, with paging, and then use the id's for the child fetch. It's of course a little tricky, but very doable, as pages in general aren't of size 1000 :)

    The CTE approach in sqlserver has a pitfall though, we ran into that with a rather complex query which had dupes in the resultset (or could have dupes). ROW_NUMBER() then returns the wrong result if you don't construct the query properly (read: more complex than I illustrated).
    See:
    http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=45034&ThreadID=8146
    We switch to temp tables in that situation so the user doesn't know better, but it's a bit of a pain, as it CAN be solved but it would require a routine which basicly re-do's the SELECT statement generation.

  • The real question is why is it that not until Microsoft experiences a problem developing with a product (Entity Frameworks in this case) with another product (SQL Server), only then do they actually consider fixing it.

    We developers have been complaining about poor paging support for a long long time now.

Comments have been disabled for this content.