SQL pagination with hierarchical tables

         Some time ago I was in quite a pickle to paginate over a database table, which contained hierarchical data, with the requirement of pagination done on a specific level of the hierarchy. I had no ownership of the DB and had to do everything from the side of the code. I've put quite a bit of time into looking for a solution, finally a colleague has proposed a solution I'm describing here.

Scenerio:

·         No control over the database

·         Hierarchical data in a flat table

·         Specific hierarchy level should be paginated and returned (parent nodes of specific paged nodes and children only of the paged nodes)

·         Decrease the amount of data, which is sent through the wire

·         Use CTE

Table: Users

Id	Name	Level	Parent
11	Janet	1	NULL
21	Tom	1	NULL
31	Anthony	2	11
41	Jack	2	21
51	Roberta	3	31
61	Ron	3	41

In this case, if we paginate by 1 record single page we'd want to get Anthony, his parent and all his children, their children etc.

Here's one of the solution to paginating on a specific level of hierarchy:

with UsersSecondLevel as
(
select * from Users where Level = 2
), UsersSecondLevelPage as
(
select * from UsersSecodLevel
OFFSET {pageIndex * pageSize} ROWS FETCH NEXT {(pageIndex + 1) * pageSize} ROWS ONLY
), UsersSecondLevelPageWithParents as
(
select parents.* from Users parents where parents.Level = 1 and parents.Id in (select Id from UsersSecondLevelPage)
union all
select * from UsersSecondLevelPage
), UsersSecondLevelPageChildren as
(
select * from UsersSecondLevelPageWithParents
union all
Select u.* form Users u
Inner join UsersSecondLevelPageChildren c on c.Parent = u.Id and c.Level > u.Level
)
select * from UsersSecondLevelPageChildren

This can be used to get the data as a single SQL liner, for instance with the entity framework and an SQL block.
If the source of the data is a function, we may have to use a temp table as a proxy for the data to avoid repeated calls to the function.