Paging re-imagined in SQL Server

Story by Abhilash on SQL Server


When we have huge records in database, it is always wise to implement paging instead of querying all the data at once. Gone are the days were we wrote long line of code to implement paging. With SQL Server 2012, we can do that in just few lines. Here is a quick sample script to query 10 records after 50th record.

Declare @PageIndex Int    -- current page index
Declare @PageSize Int    -- fetch this much record
Declare @Offset Int    -- number of rows to skip
Set @PageIndex=5
Set @PageSize=10
Set @Offset = @PageIndex * @PageSize
Select * From DemoUser
    Order By Id Offset @Offset Rows
    Fetch Next @PageSize Rows Only


Reference: OFFSET FETCH Clause

Hope this helps.

comments powered by Disqus

Related Story   →

Populate dummy records in SQL Server

Here is a quick script to generate dummy records in SQL Server