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.
Great idea but old