Dynamic Pagination

Introduction  

This tip will provide developer do little wrap around T-SQL code and get pagination done easily on top of existing code probably.

Background 

Dynamic paging in grids is cumbersome and if someone wants to implement custom paging then that has no built in support in T-SQL and using paging for 3rd party controls is sometime is not easy, when it comes to sorting on columns of grid and doing paging makes it more difficult. I found it also slow performing at times

Using the code 

For handling pagination as I did it in one of our cloud based Products. I changed generic procedure that queries internally respective table to include 2 params @PageNo int=Null, @PageSize int=20

query would include 2 columns
,RANK() OVER (ORDER BY a.OIT_dbf_Id desc) AS Rank ,((RANK() OVER (ORDER BY a.OIT_dbf_Id desc))/(@PageSize)) + 1 as Page

Where clause will include Where Rank between

IsNull(case when
@PageNo is Null then @PageNo else case when @PageNo = 1 then 1 else
(@PageNo-1)*@PageSize +1 end end,Page)

and

IsNull(case when @PageNo is Null
then @PageNo else @PageNo * @PageSize end,Rank)

so when I want particular page I would just set the procedure filter accordingly as well I can set pagesize.

Select * from ( 
select TOP 100 Percent
a.OIT_dbf_ID
as TaskID,
......
, RANK()OVER (ORDERBY a.OIT_dbf_Id desc)ASRank
,((RANK()OVER (ORDERBY a.OIT_dbf_Id desc))/(@PageSize))+ 1 as Page
from
OIT_tbl_Tasks a Where
a.OIT_dbf_Id =IsNull(@TaskID, a.OIT_dbf_Id) order by
a.OIT_dbf_Id desc
)a
Where Rank betweenIsNull(case when @PageNo isNull then @PageNo else 
							case when @PageNo = 1 then 1 else (@PageNo-1)*@PageSize +1 end
			      end,Page)andIsNull(casewhen @PageNo isNullthen @PageNo else @PageNo * @PageSize end,Rank)

Points of Interest

This I used and found performance improvement is pagination by 2-3 times compared to 3rd party controls. I am going to add caching to it i.e. getting next and prev page and also try out jQuery datatables etc..

History

This is just first version released for your opinion