What is Pagination?

Pagination means fetching data in small chunks (pages) instead of loading thousands of rows at once.

Think of Google Search — you don't see all 10 million results on one page. You see 10 results per page, and you navigate with "Next →".

The same concept applies in SQL Server — when your table has 1 lakh+ rows, fetching all of them at once is:

Slow for the user
Heavy on memory
Wasteful on network bandwidth

Pagination solves all three problems.

Before SQL Server 2012 — The Old Way

Before 2012, developers used ROW_NUMBER() with a CTE or subquery. It worked, but was verbose and tricky:

-- Old Method (SQL Server 2005, 2008)
WITH CTE_Employees AS (
    SELECT 
        EmployeeID,
        Name,
        Department,
        Salary,
        ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum
    FROM Employees
)
SELECT EmployeeID, Name, Department, Salary
FROM CTE_Employees
WHERE RowNum BETWEEN 11 AND 20;  -- Page 2 (10 rows per page)

This works, but it's not elegant. You have to manually calculate the BETWEEN range for every page.

SQL Server 2012 — The Modern Way

SQL Server 2012 introduced the OFFSET ... FETCH NEXT clause as part of the ORDER BY syntax. Clean, readable, and ANSI-standard.

Syntax:

SELECT columns
FROM TableName
ORDER BY SomeColumn
OFFSET  @SkipRows  ROWS           -- How many rows to skip
FETCH NEXT  @PageSize  ROWS ONLY; -- How many rows to return

The Formula:

OFFSET = (PageNumber - 1) * PageSize

Page	PageSize	OFFSET Calculation	OFFSET Value
1	10	(1-1) * 10	0
2	10	(2-1) * 10	10
3	10	(3-1) * 10	20
5	10	(5-1) * 10	40

Setting Up Our Demo Table

Let's create a realistic demo using a table variable (@TableVariable) — no need to create any physical table!

-- ============================================
-- STEP 1: Declare and populate a demo table
-- ============================================

DECLARE @Employees TABLE (
    EmployeeID   INT,
    Name         NVARCHAR(100),
    Department   NVARCHAR(50),
    City         NVARCHAR(50),
    Salary       DECIMAL(10,2),
    JoiningDate  DATE
);

-- Insert 50 sample employees
INSERT INTO @Employees (EmployeeID, Name, Department, City, Salary, JoiningDate)
VALUES
(1,  'Aarav Sharma',    'Engineering',  'Mumbai',    85000, '2018-03-15'),
(2,  'Priya Mehta',     'HR',           'Delhi',     55000, '2019-07-22'),
(3,  'Rohan Verma',     'Finance',      'Bangalore', 72000, '2017-11-01'),
(4,  'Sneha Joshi',     'Engineering',  'Pune',      91000, '2020-01-10'),
(5,  'Arjun Patel',     'Marketing',    'Ahmedabad', 63000, '2018-09-05'),
(6,  'Kavya Nair',      'Engineering',  'Chennai',   88000, '2021-02-14'),
(7,  'Vikram Singh',    'Finance',      'Mumbai',    76000, '2016-06-30'),
(8,  'Deepika Rao',     'HR',           'Hyderabad', 52000, '2022-03-18'),
(9,  'Ankit Gupta',     'Engineering',  'Delhi',     95000, '2019-08-25'),
(10, 'Neha Tiwari',     'Marketing',    'Pune',      61000, '2020-12-07'),
(11, 'Rahul Kumar',     'Engineering',  'Bangalore', 83000, '2017-04-19'),
(12, 'Pooja Desai',     'Finance',      'Mumbai',    71000, '2018-10-11'),
(13, 'Karan Malhotra',  'HR',           'Delhi',     58000, '2021-05-23'),
(14, 'Aisha Khan',      'Engineering',  'Chennai',   89000, '2016-12-01'),
(15, 'Suresh Iyer',     'Marketing',    'Hyderabad', 67000, '2019-02-28'),
(16, 'Meera Pillai',    'Engineering',  'Pune',      92000, '2022-07-14'),
(17, 'Ravi Shankar',    'Finance',      'Bangalore', 74000, '2017-09-09'),
(18, 'Swati Bose',      'HR',           'Mumbai',    53000, '2020-04-16'),
(19, 'Dev Kapoor',      'Engineering',  'Delhi',     86000, '2018-01-30'),
(20, 'Nisha Pillai',    'Marketing',    'Ahmedabad', 65000, '2021-11-03'),
(21, 'Amit Saxena',     'Engineering',  'Chennai',   90000, '2019-06-17'),
(22, 'Sonia Reddy',     'Finance',      'Hyderabad', 78000, '2016-08-22'),
(23, 'Rohit Bhatt',     'HR',           'Pune',      56000, '2022-01-09'),
(24, 'Tanvi Shah',      'Engineering',  'Mumbai',    87000, '2017-03-14'),
(25, 'Manoj Yadav',     'Marketing',    'Delhi',     62000, '2020-09-28'),
(26, 'Priya Agarwal',   'Engineering',  'Bangalore', 93000, '2021-04-05'),
(27, 'Saurabh Dubey',   'Finance',      'Chennai',   73000, '2018-07-21'),
(28, 'Ananya Menon',    'HR',           'Hyderabad', 54000, '2019-10-15'),
(29, 'Vivek Chandra',   'Engineering',  'Pune',      84000, '2016-05-11'),
(30, 'Kriti Sharma',    'Marketing',    'Mumbai',    68000, '2022-08-25'),
(31, 'Shiv Kumar',      'Engineering',  'Delhi',     96000, '2017-12-19'),
(32, 'Divya Nair',      'Finance',      'Bangalore', 77000, '2018-03-07'),
(33, 'Nikhil Jain',     'HR',           'Chennai',   59000, '2020-07-31'),
(34, 'Prachi Tiwari',   'Engineering',  'Ahmedabad', 88000, '2019-01-14'),
(35, 'Sachin Patil',    'Marketing',    'Hyderabad', 64000, '2021-09-02'),
(36, 'Isha Srivastava', 'Engineering',  'Pune',      91000, '2016-11-28'),
(37, 'Gaurav Mishra',   'Finance',      'Mumbai',    75000, '2017-06-06'),
(38, 'Tejal Kulkarni',  'HR',           'Delhi',     51000, '2022-04-13'),
(39, 'Harsh Gupta',     'Engineering',  'Bangalore', 94000, '2018-08-20'),
(40, 'Pallavi Joshi',   'Marketing',    'Chennai',   66000, '2020-02-24'),
(41, 'Varun Mehta',     'Engineering',  'Hyderabad', 85000, '2019-05-08'),
(42, 'Snehal Bapat',    'Finance',      'Pune',      72000, '2021-12-01'),
(43, 'Ankush Roy',      'HR',           'Mumbai',    57000, '2016-09-17'),
(44, 'Nandini Singh',   'Engineering',  'Delhi',     89000, '2017-01-25'),
(45, 'Parth Shah',      'Marketing',    'Bangalore', 63000, '2022-06-11'),
(46, 'Leena Kapoor',    'Engineering',  'Chennai',   92000, '2018-04-29'),
(47, 'Yash Pandey',     'Finance',      'Ahmedabad', 76000, '2020-10-18'),
(48, 'Mona Trivedi',    'HR',           'Hyderabad', 55000, '2019-03-22'),
(49, 'Aryan Verma',     'Engineering',  'Pune',      97000, '2021-07-07'),
(50, 'Zara Ahmed',      'Marketing',    'Mumbai',    69000, '2016-02-14');

Example 1 — Basic Pagination (Page 1)

-- ============================================
-- Get Page 1 — First 10 employees
-- ============================================

SELECT 
    EmployeeID,
    Name,
    Department,
    City,
    Salary
FROM @Employees
ORDER BY EmployeeID          -- ORDER BY is MANDATORY for OFFSET-FETCH

OFFSET 0 ROWS                -- Skip 0 rows (start from beginning)
FETCH NEXT 10 ROWS ONLY;     -- Return only 10 rows

Result: Returns rows 1–10 (EmployeeID 1 to 10).

Example 2 — Page 2, Page 3

-- ============================================
-- Get Page 2 — Employees 11 to 20
-- ============================================

SELECT EmployeeID, Name, Department, City, Salary
FROM @Employees
ORDER BY EmployeeID
OFFSET 10 ROWS               -- Skip first 10 rows
FETCH NEXT 10 ROWS ONLY;     -- Fetch next 10 = rows 11–20

-- ============================================
-- Get Page 3 — Employees 21 to 30
-- ============================================

SELECT EmployeeID, Name, Department, City, Salary
FROM @Employees
ORDER BY EmployeeID
OFFSET 20 ROWS               -- Skip first 20 rows
FETCH NEXT 10 ROWS ONLY;     -- Fetch next 10 = rows 21–30

Example 3 — Dynamic Pagination with Variables

This is what you'll actually use in stored procedures and applications:

-- ============================================
-- Dynamic Pagination — Reusable Pattern
-- ============================================

DECLARE @PageNumber  INT = 3;    -- User wants Page 3
DECLARE @PageSize    INT = 10;   -- 10 rows per page

DECLARE @OffsetRows  INT = (@PageNumber - 1) * @PageSize;
-- For Page 3: (3-1)*10 = 20 → Skip 20 rows

SELECT 
    EmployeeID,
    Name,
    Department,
    City,
    Salary,
    JoiningDate
FROM @Employees
ORDER BY EmployeeID
OFFSET  @OffsetRows  ROWS
FETCH NEXT  @PageSize  ROWS ONLY;

Result: Returns rows 21–30. Change @PageNumber to any value — it works automatically.

Example 4 — Pagination with Filtering

Real-world apps always have filters. Pagination works perfectly with WHERE clauses:

-- ============================================
-- Paginated results for Engineering department only
-- ============================================

DECLARE @PageNumber  INT = 1;
DECLARE @PageSize    INT = 5;
DECLARE @Department  NVARCHAR(50) = 'Engineering';

DECLARE @OffsetRows  INT = (@PageNumber - 1) * @PageSize;

SELECT 
    EmployeeID,
    Name,
    Department,
    City,
    Salary
FROM @Employees
WHERE Department = @Department   -- Filter first, then paginate
ORDER BY Salary DESC             -- Highest paid first
OFFSET  @OffsetRows  ROWS
FETCH NEXT  @PageSize  ROWS ONLY;

Result: Top 5 highest-paid Engineers on Page 1. Change @PageNumber = 2 for next 5.

Example 5 — Pagination with Total Row Count

A complete pagination implementation always needs the total count so you can show "Page 3 of 12". Here are two patterns:

Pattern A — Two Separate Queries

DECLARE @PageNumber  INT = 2;
DECLARE @PageSize    INT = 10;
DECLARE @OffsetRows  INT = (@PageNumber - 1) * @PageSize;

-- Query 1: Get total count
SELECT COUNT(*) AS TotalRows
FROM @Employees
WHERE Department = 'Engineering';

-- Query 2: Get paginated data
SELECT 
    EmployeeID,
    Name,
    Department,
    Salary
FROM @Employees
WHERE Department = 'Engineering'
ORDER BY EmployeeID
OFFSET  @OffsetRows  ROWS
FETCH NEXT  @PageSize  ROWS ONLY;

Pattern B — Total Count Inline with COUNT OVER()

-- Single query — returns data + total count in every row
DECLARE @PageNumber  INT = 1;
DECLARE @PageSize    INT = 10;
DECLARE @OffsetRows  INT = (@PageNumber - 1) * @PageSize;

SELECT 
    EmployeeID,
    Name,
    Department,
    City,
    Salary,
    COUNT(*) OVER () AS TotalRows   -- Total count in every row (no subquery needed)
FROM @Employees
ORDER BY EmployeeID
OFFSET  @OffsetRows  ROWS
FETCH NEXT  @PageSize  ROWS ONLY;

Why Pattern B is better: Single round trip to SQL Server. Your application reads TotalRows from the first row and calculates total pages:

Total Pages = CEILING(TotalRows / PageSize)

Example 6 — Reusable Pagination Stored Procedure

This is production-ready code:

-- ============================================
-- Stored Procedure: usp_GetEmployeesPaged
-- ============================================

CREATE PROCEDURE usp_GetEmployeesPaged
    @PageNumber    INT            = 1,
    @PageSize      INT            = 10,
    @Department    NVARCHAR(50)   = NULL,    -- Optional filter
    @SortColumn    NVARCHAR(50)   = 'EmployeeID',
    @SortDirection NVARCHAR(4)    = 'ASC'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @OffsetRows INT = (@PageNumber - 1) * @PageSize;

    -- Validate inputs
    IF @PageNumber < 1  SET @PageNumber = 1;
    IF @PageSize   < 1  SET @PageSize   = 10;
    IF @PageSize   > 100 SET @PageSize  = 100;  -- Safety cap

    SELECT 
        EmployeeID,
        Name,
        Department,
        City,
        Salary,
        JoiningDate,
        COUNT(*) OVER () AS TotalRows
    FROM Employees   -- Use your actual table here
    WHERE (@Department IS NULL OR Department = @Department)
    ORDER BY
        CASE WHEN @SortColumn = 'Name'       AND @SortDirection = 'ASC'  THEN Name       END ASC,
        CASE WHEN @SortColumn = 'Name'       AND @SortDirection = 'DESC' THEN Name       END DESC,
        CASE WHEN @SortColumn = 'Salary'     AND @SortDirection = 'ASC'  THEN Salary     END ASC,
        CASE WHEN @SortColumn = 'Salary'     AND @SortDirection = 'DESC' THEN Salary     END DESC,
        CASE WHEN @SortColumn = 'EmployeeID' OR  @SortColumn IS NULL     THEN EmployeeID END ASC
    OFFSET  @OffsetRows  ROWS
    FETCH NEXT  @PageSize  ROWS ONLY;
END;
GO

-- Usage:
EXEC usp_GetEmployeesPaged @PageNumber = 2, @PageSize = 10;
EXEC usp_GetEmployeesPaged @PageNumber = 1, @PageSize = 5, @Department = 'Finance';
EXEC usp_GetEmployeesPaged @PageNumber = 1, @PageSize = 10, @SortColumn = 'Salary', @SortDirection = 'DESC';

Example 7 — Old Method vs New Method Comparison

It's worth keeping both approaches in your toolkit, especially if you maintain legacy codebases:

DECLARE @PageNumber  INT = 3;
DECLARE @PageSize    INT = 10;

-- ============================================
-- OLD METHOD — ROW_NUMBER() (SQL Server 2005+)
-- ============================================

DECLARE @StartRow INT = (@PageNumber - 1) * @PageSize + 1;
DECLARE @EndRow   INT = @PageNumber * @PageSize;

WITH RankedEmployees AS (
    SELECT 
        EmployeeID,
        Name,
        Department,
        Salary,
        ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum
    FROM @Employees
)
SELECT EmployeeID, Name, Department, Salary
FROM RankedEmployees
WHERE RowNum BETWEEN @StartRow AND @EndRow;


-- ============================================
-- NEW METHOD — OFFSET FETCH (SQL Server 2012+)
-- ============================================

DECLARE @OffsetRows INT = (@PageNumber - 1) * @PageSize;

SELECT EmployeeID, Name, Department, Salary
FROM @Employees
ORDER BY EmployeeID
OFFSET  @OffsetRows  ROWS
FETCH NEXT  @PageSize  ROWS ONLY;

Both return the same result (rows 21–30). The new method is clearly more readable.

Performance Best Practices

1. Always Have an Index on the ORDER BY Column

The most common performance killer in pagination is a missing index on the sort column. Without it, SQL Server does a full table scan + sort for every page request.

-- If paginating by EmployeeID (clustered key — already indexed, good)
-- If paginating by Salary or Name, create a covering index:

CREATE INDEX IX_Employees_Salary
ON Employees (Salary)
INCLUDE (EmployeeID, Name, Department, City, JoiningDate);

2. Avoid Large OFFSET Values

The deeper the page, the slower it gets. With OFFSET 90000 ROWS, SQL Server still "reads past" those 90,000 rows before skipping them — it doesn't teleport.

For very deep pagination (page 500+ in a massive table), consider a keyset pagination (seek method) as an alternative for extreme cases.

3. Use WHERE Before You Paginate

Filter early — always apply WHERE conditions before pagination kicks in. SQL Server's optimizer generally handles this well, but always verify your execution plan.

4. Never Use SELECT * with Pagination

Specify only the columns you need. For paginated APIs returning 20 rows, fetching 50 columns when you need 6 is wasteful.

Important Rules to Remember

Rule	Why it Matters
`ORDER BY` is mandatory	`OFFSET FETCH` without `ORDER BY` throws an error. Pagination is meaningless without a defined sort order.
`OFFSET 0` is valid	Page 1 skips zero rows. No special handling needed.
`FETCH NEXT` only, not `FETCH FIRST`	Both are SQL standard, but SQL Server supports `NEXT`. Use `NEXT`.
Works with `TOP` or alone	Don't mix `TOP` and `OFFSET FETCH` — they conflict. Use one or the other.
SQL Server 2012+ only	`OFFSET FETCH` syntax does not exist in 2008 or older. Use `ROW_NUMBER()` for backward compatibility.

Quick Reference Cheat Sheet

-- PAGE 1 (10 per page)
ORDER BY EmployeeID OFFSET 0  ROWS FETCH NEXT 10 ROWS ONLY;

-- PAGE 2
ORDER BY EmployeeID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- PAGE N (dynamic)
DECLARE @N INT = 5, @Size INT = 10;
ORDER BY EmployeeID OFFSET (@N-1)*@Size ROWS FETCH NEXT @Size ROWS ONLY;

-- With total count
SELECT *, COUNT(*) OVER () AS TotalRows
FROM YourTable
ORDER BY ID
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Summary

SQL Server 2012's OFFSET ... FETCH NEXT is the clean, modern way to implement pagination. It is:

Readable — the intent is clear from the syntax itself
ANSI-standard — works across databases with minor tweaks
Flexible — pairs perfectly with WHERE, JOIN, aggregations, and COUNT(*) OVER()
Performant — with the right indexes, scales well for most real-world data volumes

For any new SQL Server 2012+ project, there is no reason to use the old ROW_NUMBER() method. Reserve that only when you need to support SQL Server 2008 or older environments.

Found this helpful? More SQL Server deep-dives on HelpInData.

Tags: #SQLServer #Pagination #OFFSETFETCH #SQLServer2012 #TSQL #DBA #SQLTips

Himanshu bhai, blog ready hai — full copy-paste format mein! 🔥

What's covered:

Concept with visual diagram
Table variable with 50 realistic rows (no physical table needed)
7 progressive examples from basic to production SP
Old vs new method comparison
Performance tips + rules table + cheat sheet

Agar chahiye toh iska Hindi/Hinglish version bhi bana sakta hoon ya Firebase pe deploy karne wala HTML version bhi — bas bolo!

Results for "OFFSETFETCH"

SQL Server Pagination