Paging is one of the most highly required features during application development process. Microsoft has introduced various features since the times of SQL Server 2000 to the latest version of SQL Server 2014. In SQL Server 2012 a new feature was added to the ORDER BY clause. It allows to write more efficient query since the query optimization standpoint and makes work easier for anyone who writes in T-SQL and needs data paging implemented.

Before creating script for paging, you should create a set of test data. You can find the script for creating temporary table and inserting test data to the temporary table below:

1) Creating temporary table #Tmp:

CREATE TABLE #Tmp( ID int NOT NULL IDENTITY(1,1), Name nvarchar(50) NOT NULL );

GO

2) Inserting 100 rows into the temporary table:

INSERT INTO #Tmp (Name) VALUES ('Example No' + CONVERT(VARCHAR,ISNULL(@@IDENTITY, 0)))

GO 100

Test data is now ready and paging script can be executed. Take a look at the below code snippet:

DECLARE @PageNumber AS INT ,

@RowspPage AS INT

SET @PageNumber = 1 SET @RowspPage = 10

SELECT ID, Name

FROM #Tmp

ORDER BY ID OFFSET ((@PageNumber - 1) * @RowspPage)

ROWS FETCH NEXT @RowspPage ROWS ONLY;

This example utilizes the OFFSET function. It is important to mention that the first record returned will be in the “zero” position. In this case you can define the @PageNumber variable with value 1 and the @RowspPage variable with value 10. So that you receive the following data:

– the first row in the 1 position

– the last row in the 10 position