Andres Aguiar's Weblog

Right here, right now

Stories

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 ;).


Posted: May 21 2007, 11:36 AM by aaguiar | with 5 comment(s)
Filed under: ,

Comments

FransBouma said:

Let's hope they sent the scientists on a holiday break when the rest of the team hammers down the API details ;)
# May 21, 2007 9:48 AM

Mike Griffin said:

We have implemented auto paging for all databases that support it, I agree the syntax is nutz though for this. http://www.entityspaces.net/blog/PagingBeingBuiltRightIntoEntitySpaces2007.aspx
# May 21, 2007 1:40 PM

aaguiar said:

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.

# May 21, 2007 2:20 PM

FransBouma said:

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.
# May 21, 2007 4:02 PM

Eric said:

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.

# May 23, 2007 3:11 PM