Phân trang (Pagination) trong SQL Server — Hướng dẫn chi tiết cho Data Engineer

Mục tiêu: giải thích các kỹ thuật phân trang phổ biến trong SQL Server (OFFSET/FETCH, ROW_NUMBER(), keyset pagination), điểm mạnh/khuyết điểm, tối ưu hiệu năng, và cung cấp ví dụ thực tế kèm stored procedure mẫu để dùng trong ứng dụng.

""

Mục lục

1. Tổng quan & yêu cầu cơ bản
2. Khi nào cần phân trang?
3. Cách 1 — OFFSET ... FETCH (SQL Server 2012+)
4. Cách 2 — ROW_NUMBER()
5. Cách 3 — Keyset pagination (seek method)
6. So sánh, ưu/nhược điểm
7. Tối ưu hiệu năng & chỉ mục
8. Lấy tổng số bản ghi
9. Stored procedure mẫu
10. Lỗi thường gặp & best practices

1. Tổng quan & yêu cầu

Phân trang là kỹ thuật chia kết quả truy vấn thành nhiều trang nhỏ nhằm giảm tải và tăng hiệu năng hệ thống. ORDER BY là bắt buộc.

2. Khi cần phân trang

• API trả danh sách
• UI hiển thị bảng
• Dataset lớn

3. OFFSET / FETCH

SELECT Id, Name, CreatedAt
FROM dbo.Orders
ORDER BY CreatedAt DESC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;

4. ROW_NUMBER()

WITH Ordered AS (
  SELECT Id, Name, CreatedAt,
         ROW_NUMBER() OVER (ORDER BY CreatedAt DESC) AS rn
  FROM dbo.Orders
)
SELECT *
FROM Ordered
WHERE rn BETWEEN @StartRow AND @EndRow;

5. Keyset pagination

Seek bằng giá trị cuối cùng:

SELECT TOP (10) Id, CreatedAt
FROM dbo.Orders
WHERE (CreatedAt < @LastCreatedAt)
   OR (CreatedAt = @LastCreatedAt AND Id < @LastId)
ORDER BY CreatedAt DESC, Id DESC;

6. So sánh

• OFFSET: dễ dùng
• ROW_NUMBER: linh hoạt
• Keyset: hiệu năng tốt nhất

7. Indexing

CREATE NONCLUSTERED INDEX IX_Orders_CreatedAt_Id
ON dbo.Orders (CreatedAt DESC, Id DESC);

8. Total count

Dùng COUNT(*) có thể chậm; cân nhắc cache.

9. Stored procedure minh họa

CREATE PROCEDURE dbo.GetOrdersPaged
  @PageNumber INT = 1,
  @PageSize INT = 20
AS
BEGIN
  SET NOCOUNT ON;
  SELECT Id, Amount, CreatedAt
  FROM dbo.Orders
  ORDER BY CreatedAt DESC, Id DESC
  OFFSET @PageSize * (@PageNumber - 1)
  ROWS FETCH NEXT @PageSize ROWS ONLY;
END;

10. Best practices

• ORDER BY có tính xác định
• PageSize hợp lý
• Dùng keyset nếu dataset lớn
• Dùng covering index