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
