Các Kỹ Thuật Phân Trang Hiệu Quả Sử Dụng Stored Procedure trong SQL Server

tieudoan_208 · July 7, 2026, 1:46am

1. Phân Trang Là Gì và Tại Sao Quan Trọng?

Phân trang (Pagination) là kỹ thuật chia nhỏ tập kết quả lớn từ cơ sở dữ liệu thành các “trang” nhỏ hơn, giúp ứng dụng chỉ trả về một phần dữ liệu tại một thời điểm thay vì toàn bộ dataset.

Tại Sao Cần Phân Trang?

Hiệu năng : Giảm lượng dữ liệu truyền qua mạng và xử lý ở client
Bộ nhớ : Tránh load toàn bộ hàng triệu bản ghi vào RAM
UX tốt hơn : Người dùng nhận được phản hồi nhanh hơn
Scalability : Hệ thống xử lý được khi dữ liệu tăng trưởng

Ví dụ điển hình: Một bảng Orders có 10 triệu bản ghi. Nếu không phân trang, mỗi query trả về 10M rows là thảm họa về hiệu năng.

Tại Sao Dùng Stored Procedure?

Stored Procedure (SP) mang lại nhiều lợi thế khi implement pagination:

Lợi thế	Mô tả
Tái sử dụng	Viết một lần, gọi từ nhiều nơi
Bảo mật	Kiểm soát quyền truy cập, chống SQL Injection
Hiệu năng	Execution plan được cache lại
Bảo trì	Logic tập trung ở database layer
Network traffic	Giảm lượng SQL gửi từ app

2. Kỹ Thuật 1: OFFSET-FETCH

Đây là cách phổ biến nhất và được giới thiệu từ SQL Server 2012. Cú pháp gọn gàng, dễ hiểu.

Cú Pháp Cơ Bản

SELECT column1, column2, ...
FROM TableName
ORDER BY column1
OFFSET @Skip ROWS
FETCH NEXT @PageSize ROWS ONLY;
OFFSET @Skip ROWS: Bỏ qua bao nhiêu dòng đầu tiên

FETCH NEXT @PageSize ROWS ONLY: Lấy bao nhiêu dòng tiếp theo

Công thức tính OFFSET:

OFFSET = (PageNumber - 1) * PageSize

Stored Procedure Hoàn Chỉnh

CREATE OR ALTER PROCEDURE usp_GetProducts_OffsetFetch
    @PageNumber   INT = 1,       -- Trang hiện tại (bắt đầu từ 1)
    @PageSize     INT = 10,      -- Số bản ghi mỗi trang
    @SortColumn   NVARCHAR(50) = N'ProductID',
    @SortOrder    NVARCHAR(4)  = N'ASC'
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate input
    IF @PageNumber < 1 SET @PageNumber = 1;
    IF @PageSize   < 1 SET @PageSize   = 10;
    IF @PageSize   > 200 SET @PageSize = 200; -- Giới hạn tối đa

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

    SELECT
        ProductID,
        ProductName,
        CategoryID,
        UnitPrice,
        UnitsInStock,
        CreatedDate
    FROM dbo.Products
    WHERE Discontinued = 0      -- Filter ví dụ
    ORDER BY
        CASE WHEN @SortColumn = 'ProductName' AND @SortOrder = 'ASC'
             THEN ProductName END ASC,
        CASE WHEN @SortColumn = 'ProductName' AND @SortOrder = 'DESC'
             THEN ProductName END DESC,
        CASE WHEN @SortColumn = 'UnitPrice' AND @SortOrder = 'ASC'
             THEN UnitPrice END ASC,
        CASE WHEN @SortColumn = 'UnitPrice' AND @SortOrder = 'DESC'
             THEN UnitPrice END DESC,
        ProductID ASC   -- Tiebreaker mặc định
    OFFSET @Skip ROWS
    FETCH NEXT @PageSize ROWS ONLY;

END;
GO

Gọi Stored Procedure

-- Lấy trang 3, mỗi trang 20 sản phẩm
EXEC usp_GetProducts_OffsetFetch
    @PageNumber = 3,
    @PageSize   = 20,
    @SortColumn = 'UnitPrice',
    @SortOrder  = 'DESC';

Ưu và Nhược Điểm

Ưu điểm	Nhược điểm
Cú pháp đơn giản, dễ đọc	Hiệu năng giảm dần ở trang cuối
Hỗ trợ random access (nhảy trang tùy ý)	Phải scan qua toàn bộ OFFSET rows
Dễ tích hợp với UI	Không phù hợp với dataset hàng triệu rows ở trang lớn

Lưu ý quan trọng: ORDER BY là bắt buộc khi dùng OFFSET-FETCH. Không có ORDER BY sẽ gây lỗi.

3. Kỹ Thuật 2: ROW_NUMBER() với CTE

Kỹ thuật này dùng Common Table Expression (CTE) kết hợp hàm ROW_NUMBER() — phù hợp với SQL Server 2005+ và cho phép kiểm soát chi tiết hơn.

Cơ Chế Hoạt Động

WITH CTE AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY CreatedDate DESC) AS RowNum
    FROM dbo.Orders
)
SELECT * FROM CTE
WHERE RowNum BETWEEN @StartRow AND @EndRow;

Stored Procedure Đầy Đủ

CREATE OR ALTER PROCEDURE usp_GetOrders_RowNumber
    @PageNumber   INT = 1,
    @PageSize     INT = 10,
    @CustomerID   NVARCHAR(10) = NULL,     -- Filter tuỳ chọn
    @FromDate     DATE = NULL,
    @ToDate       DATE = NULL,
    @TotalRecords INT OUTPUT               -- Trả về tổng số bản ghi
AS
BEGIN
    SET NOCOUNT ON;

    IF @PageNumber < 1 SET @PageNumber = 1;
    IF @PageSize   < 1 SET @PageSize   = 10;

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

    -- Sử dụng CTE để đánh số dòng
    WITH OrderedOrders AS
    (
        SELECT
            o.OrderID,
            o.CustomerID,
            c.CompanyName,
            o.OrderDate,
            o.ShippedDate,
            o.Freight,
            o.ShipCountry,
            ROW_NUMBER() OVER (
                ORDER BY o.OrderDate DESC, o.OrderID DESC
            ) AS RowNum,
            COUNT(*) OVER () AS TotalCount  -- Tổng số dòng (không cần query riêng)
        FROM
            dbo.Orders       o
            INNER JOIN dbo.Customers c ON c.CustomerID = o.CustomerID
        WHERE
            (@CustomerID IS NULL OR o.CustomerID = @CustomerID)
            AND (@FromDate IS NULL OR o.OrderDate >= @FromDate)
            AND (@ToDate   IS NULL OR o.OrderDate <= @ToDate)
    )
    SELECT
        OrderID,
        CustomerID,
        CompanyName,
        OrderDate,
        ShippedDate,
        Freight,
        ShipCountry,
        TotalCount AS TotalRecords
    FROM OrderedOrders
    WHERE RowNum BETWEEN @StartRow AND @EndRow
    ORDER BY RowNum;

    -- Gán tổng số vào OUTPUT parameter
    SELECT @TotalRecords = COUNT(*)
    FROM dbo.Orders
    WHERE
        (@CustomerID IS NULL OR CustomerID = @CustomerID)
        AND (@FromDate IS NULL OR OrderDate >= @FromDate)
        AND (@ToDate   IS NULL OR OrderDate <= @ToDate);

END;
GO

Gọi SP Với OUTPUT Parameter

DECLARE @Total INT;

EXEC usp_GetOrders_RowNumber
    @PageNumber   = 2,
    @PageSize     = 15,
    @CustomerID   = 'ALFKI',
    @FromDate     = '2024-01-01',
    @ToDate       = '2024-12-31',
    @TotalRecords = @Total OUTPUT;

SELECT @Total AS TotalOrders;  -- Kết quả: ví dụ 45
-- TotalPages = CEILING(45.0 / 15) = 3

Mẹo: Tính TotalPages Ngay Trong SP

-- Thêm vào cuối SP
SELECT 
    @TotalRecords AS TotalRecords,
    @PageNumber   AS CurrentPage,
    @PageSize     AS PageSize,
    CEILING(CAST(@TotalRecords AS FLOAT) / @PageSize) AS TotalPages;

4. Kỹ Thuật 3: Keyset Pagination (Seek Method)

Đây là kỹ thuật hiệu năng cao nhất cho dataset lớn, còn gọi là “Seek Method” hay “Cursor-based Pagination” .

Nguyên Lý Hoạt Động

Thay vì bỏ qua N rows đầu tiên (tốn kém), Keyset Pagination ghi nhớ giá trị cuối cùng của trang trước, rồi query “from there on”:

Trang 1: WHERE ID > 0 ORDER BY ID → trả về ID = 1…20

Trang 2: WHERE ID > 20 ORDER BY ID → trả về ID = 21…40

Trang 3: WHERE ID > 40 ORDER BY ID → trả về ID = 41…60

Không cần scan qua 40 row đầu để lấy trang 3!

Stored Procedure

CREATE OR ALTER PROCEDURE usp_GetProducts_Keyset
    @LastID       INT  = 0,        -- ID cuối cùng của trang trước (0 = trang đầu)
    @LastPrice    DECIMAL(10,2) = NULL, -- Giá cuối (nếu sort theo Price)
    @PageSize     INT  = 20,
    @SortOrder    NVARCHAR(4)  = N'ASC'
AS
BEGIN
    SET NOCOUNT ON;

    IF @PageSize < 1  SET @PageSize = 20;
    IF @PageSize > 100 SET @PageSize = 100;

    IF @SortOrder = 'ASC'
    BEGIN
        SELECT TOP (@PageSize)
            ProductID,
            ProductName,
            UnitPrice,
            UnitsInStock
        FROM dbo.Products
        WHERE Discontinued = 0
          AND ProductID > @LastID      -- Điều kiện Keyset!
        ORDER BY ProductID ASC;
    END
    ELSE
    BEGIN
        SELECT TOP (@PageSize)
            ProductID,
            ProductName,
            UnitPrice,
            UnitsInStock
        FROM dbo.Products
        WHERE Discontinued = 0
          AND ProductID < @LastID
        ORDER BY ProductID DESC;
    END

END;
GO

Keyset Với Sort Phức Tạp (Composite Key)

Khi sort theo nhiều cột, cần truyền giá trị của toàn bộ sort key:

CREATE OR ALTER PROCEDURE usp_GetProducts_KeysetByPrice
    @LastUnitPrice   DECIMAL(10,2) = NULL,
    @LastProductID   INT = 0,              -- Tiebreaker
    @PageSize        INT = 20
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (@PageSize)
        ProductID,
        ProductName,
        UnitPrice
    FROM dbo.Products
    WHERE Discontinued = 0
      AND (
            @LastUnitPrice IS NULL           -- Trang đầu tiên
            OR UnitPrice > @LastUnitPrice    -- Giá lớn hơn
            OR (
                UnitPrice = @LastUnitPrice   -- Giá bằng nhau → so sánh ID
                AND ProductID > @LastProductID
            )
          )
    ORDER BY UnitPrice ASC, ProductID ASC;

END;
GO

Gọi SP Theo Trang

-- === TRANG 1 ===
EXEC usp_GetProducts_KeysetByPrice
    @LastUnitPrice = NULL,
    @LastProductID = 0,
    @PageSize = 5;
-- Kết quả: (UnitPrice=5.0, ID=1), (8.0, ID=4), (9.2, ID=7), (10.0, ID=2), (10.0, ID=5)

-- === TRANG 2 ===
-- LastUnitPrice = 10.0, LastProductID = 5 (lấy từ dòng cuối trang 1)
EXEC usp_GetProducts_KeysetByPrice
    @LastUnitPrice = 10.0,
    @LastProductID = 5,
    @PageSize = 5;

So Sánh Với OFFSET

Dataset: 5,000,000 rows, lấy trang 10,000 (PageSize=20)

OFFSET-FETCH:

→ SQL phải scan qua 199,980 rows rồi mới lấy 20 rows

→ Execution time: ~800ms

Keyset Pagination:

→ SQL tìm thẳng đến vị trí theo index

→ Execution time: ~2ms

Best For: Infinite scroll, “Load More” button, API với cursor-based navigation

5. Kỹ Thuật 4: Phân Trang Với Tổng Số Bản Ghi

Trong nhiều UI, cần hiển thị “Trang 3 / 50” hoặc “Hiển thị 41-60 của 1,000 kết quả” . Đây là SP kết hợp dữ liệu và metadata:

Approach 1: Hai Result Sets

CREATE OR ALTER PROCEDURE usp_GetEmployees_WithTotal
    @PageNumber   INT = 1,
    @PageSize     INT = 10,
    @SearchTerm   NVARCHAR(100) = NULL,
    @DepartmentID INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

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

    -- Result Set 1: Dữ liệu phân trang
    SELECT
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.JobTitle,
        d.DepartmentName,
        e.HireDate,
        e.Salary
    FROM
        dbo.Employees   e
        INNER JOIN dbo.Departments d ON d.DepartmentID = e.DepartmentID
    WHERE
        e.IsActive = 1
        AND (@DepartmentID IS NULL OR e.DepartmentID = @DepartmentID)
        AND (
            @SearchTerm IS NULL
            OR e.FirstName  LIKE '%' + @SearchTerm + '%'
            OR e.LastName   LIKE '%' + @SearchTerm + '%'
            OR e.JobTitle   LIKE '%' + @SearchTerm + '%'
        )
    ORDER BY e.LastName ASC, e.FirstName ASC
    OFFSET @Skip ROWS
    FETCH NEXT @PageSize ROWS ONLY;

    -- Result Set 2: Metadata
    SELECT
        COUNT(*)                                           AS TotalRecords,
        CEILING(COUNT(*) * 1.0 / @PageSize)               AS TotalPages,
        @PageNumber                                        AS CurrentPage,
        @PageSize                                          AS PageSize,
        (@PageNumber - 1) * @PageSize + 1                 AS FromRecord,
        LEAST((@PageNumber) * @PageSize, COUNT(*))        AS ToRecord
    FROM
        dbo.Employees   e
    WHERE
        e.IsActive = 1
        AND (@DepartmentID IS NULL OR e.DepartmentID = @DepartmentID)
        AND (
            @SearchTerm IS NULL
            OR e.FirstName  LIKE '%' + @SearchTerm + '%'
            OR e.LastName   LIKE '%' + @SearchTerm + '%'
            OR e.JobTitle   LIKE '%' + @SearchTerm + '%'
        );
END;
GO

Approach 2: COUNT(*) OVER() — Chỉ 1 Query

SELECT
    EmployeeID,
    FirstName,
    LastName,
    COUNT(*) OVER() AS TotalRecords   -- Window function!
FROM dbo.Employees
WHERE IsActive = 1
ORDER BY LastName
OFFSET @Skip ROWS
FETCH NEXT @PageSize ROWS ONLY;

Lưu ý: COUNT(*) OVER() tính trên toàn bộ tập kết quả trước khi filter OFFSET/FETCH, nên vẫn chính xác.

6. So Sánh Hiệu Năng

Benchmark: Bảng 5 Triệu Bản Ghi

Kỹ thuật	Trang 1	Trang 100	Trang 1,000	Trang 10,000
OFFSET-FETCH	2ms	15ms	120ms	950ms
ROW_NUMBER() CTE	3ms	18ms	135ms	980ms
Keyset Pagination	2ms	2ms	2ms	2ms

Số liệu tham khảo, thực tế phụ thuộc vào hardware, index, và query complexity

Khi Nào Dùng Kỹ Thuật Nào?

OFFSET-FETCH

→ Dữ liệu < 500K rows

→ Cần random access (nhảy đến trang bất kỳ)

→ UI pagination truyền thống (trang 1, 2, 3…)

ROW_NUMBER() CTE

→ Cần tương thích SQL Server 2005+

→ Logic phân trang phức tạp, nhiều filter

→ Cần OUTPUT parameter để truyền tổng số

Keyset Pagination

→ Dataset > 1 triệu rows

→ Infinite scroll hoặc “Load More”

→ REST API với cursor

→ Hiệu năng là ưu tiên số 1

Kết hợp (Hybrid)

→ Dùng OFFSET cho trang nhỏ (<100)

→ Chuyển sang Keyset khi user scroll sâu

7. Best Practices và Lưu Ý

Index Strategy

– Tạo index phù hợp với sort column

-- Ví dụ: sort theo OrderDate DESC, OrderID DESC
CREATE INDEX IX_Orders_OrderDate_OrderID
    ON dbo.Orders (OrderDate DESC, OrderID DESC)
    INCLUDE (CustomerID, Freight, ShipCountry);
-- INCLUDE các cột thường SELECT để tránh Key Lookup

Tránh SELECT *

– Không nên

SELECT * FROM dbo.Products OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
--  Nên - chỉ lấy cột cần thiết
SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Validate Input Parameters

-- Luôn validate để tránh injection qua parameter
IF @PageSize NOT BETWEEN 1 AND 500
    RAISERROR('PageSize phải từ 1 đến 500', 16, 1);

IF @PageNumber < 1
    RAISERROR('PageNumber phải >= 1', 16, 1);

Sử Dụng SET NOCOUNT ON

-- Luôn đặt SET NOCOUNT ON ở đầu SP
-- Tránh gửi "X rows affected" message về client, giảm network overhead
SET NOCOUNT ON;

Parameter Sniffing — Vấn Đề Thường Gặp

-- Vấn đề: SQL Server cache execution plan lần đầu gọi
-- Nếu lần đầu gọi với @PageNumber=1, plan có thể không tốt cho @PageNumber=500

-- Giải pháp 1: OPTION (RECOMPILE) — tốn CPU hơn nhưng luôn có plan tốt
SELECT ... OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY
OPTION (RECOMPILE);

-- Giải pháp 2: Local variables
DECLARE @LocalSkip INT = @Skip;
SELECT ... OFFSET @LocalSkip ROWS FETCH NEXT @LocalPageSize ROWS ONLY;

-- Giải pháp 3: OPTIMIZE FOR
SELECT ... OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY
OPTION (OPTIMIZE FOR (@PageNumber = 1));

Kết Hợp Với Caching (Application Layer)

// C# Example với cache
public async Task<PagedResult<Product>> GetProductsAsync(int page, int size)
{
    string cacheKey = $"products:page:{page}:size:{size}";
    
    // Kiểm tra cache
    if (_cache.TryGetValue(cacheKey, out PagedResult<Product> cached))
        return cached;
    
    // Gọi stored procedure
    var result = await _db.ExecuteSpAsync<Product>(
        "usp_GetProducts_OffsetFetch",
        new { PageNumber = page, PageSize = size }
    );
    
    // Cache 60 giây cho trang không thay đổi thường xuyên
    _cache.Set(cacheKey, result, TimeSpan.FromSeconds(60));
    return result;
}

Kỹ thuật	SQL Server	Random Access	Hiệu năng lớn	Độ phức tạp
OFFSET-FETCH	2012+	Có	Kém	Thấp
ROW_NUMBER() CTE	2005+	Có	Kém	Trung
Keyset Pagination	Mọi version	Không	Tốt	Cao

Tổng kết

Bắt đầu bằng OFFSET-FETCH nếu dataset nhỏ và cần phát triển nhanh
Chuyển sang Keyset khi dataset lớn hoặc gặp vấn đề hiệu năng
Luôn có index phù hợp với ORDER BY column
Test với production data — đừng chỉ test trên dataset nhỏ
Monitor execution plans định kỳ để phát hiện vấn đề sớm

Các fen vào đây format code cho dễ nhìn nhé