How to use Paging in SQL Queries?

Paging is one of the most important tasks when developers developed applications. This article describes how to use paging in SQL queries or How to select 5-10 records?. In SQL queries we can select first 1-10  records, 10-20 records, 20-30 records etc. Summery of the article:

• SQL ROW_NUMBER() Function
• Paging using Sub Queries
• Paging using Common Table Expression (CTE)

SQL ROW_NUMBER() Function
SQL ROW_NUMBER() function is a built-in function of SQL Server. For SQL paging we need to use this.
We can implement paging in SQL in two ways

Paging using Sub Queries
Here is an example of SQL Sub Queries for paging.

SELECT * 
FROM (
      SELECT  
            CollectionID
            ,ROW_NUMBER() OVER(ORDER BY CollectionID) RowID
      FROM TestDB.dbo.Collections
) T
WHERE RowID between 5 and 10

Paging using Common Table Expression (CTE)
Here is an example of SQL Common Table Expression (CTE) for paging.

;WITH CTE as
(
      SELECT  
                  CollectionID
                  ,ROW_NUMBER() OVER(ORDER BY CollectionID) RowID
      FROM TestDB.dbo.Collections
)
SELECT * from CTE
WHERE RowID between 5 and 10

That’s all about SQL paging.