Using ROW_NUMBER() to paginate your data ... Script in T-SQL

Here making paginating functionality by Sql Server side...

Here first of all set @totalPageSize, which set how many row should be display...

Then set @pageIndex , which set the pageindex,

declare @totalPageSize int

declare @pageIndex int

declare @startIndex int

declare @endIndex int

set @totalPageSize = 10

set @pageIndex = 1

set @startIndex = (@pageIndex * @totalPageSize) + 1

set @endIndex = @startIndex + @totalPageSize

select top (@totalPageSize) z.*

from

(

select

ROW_NUMBER() OVER(ORDER BY userId DESC) AS 'RowNumber',

userId, userFirstName, userLastName,

userAddress1, userAddress2, userPhone1,

userPhone2

from [userInfo]

) as z

where z.RowNumber between @startIndex and @endIndex

RowNumber userId userFirstName userLastName userAddress1 userAddress2 userPhone1 userPhone2

11 45 A B sdf sdf 34343

12 44 G W ds ds 1121

13 43 K T Ahd Ahd 121 12

14 42 T Y Ahd Ahd 12323 34234

17 38 ehilly Bons Ahmedabad

18 37 dhilly Bons Ahmedabad

19 36 chilly Bons Ahmedabad

20 35 bhilly Bons Ahmedabad