Làm sao để viết truy vấn phân trang (paging) trong SQL Server?

Viết truy vấn phân trang (paging) trong SQL Server giúp bạn lấy một phần dữ liệu trong bảng, rất hữu ích khi làm việc với dữ liệu lớn hoặc hiển thị dữ liệu trên nhiều trang trong ứng dụng web.

Cách viết truy vấn phân trang trong SQL Server#

1. Sử dụng OFFSET ... FETCH NEXT (SQL Server 2012 trở lên)#

Đây là cách chuẩn và đơn giản nhất để phân trang.

SELECT column1, column2, ... FROM table_name ORDER BY column_sort OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;

• @PageNumber là số trang bạn muốn lấy (bắt đầu từ 1).

• @PageSize là số bản ghi trên mỗi trang.

• Câu lệnh ORDER BY bắt buộc phải có khi dùng OFFSET.

Ví dụ:#

Giả sử bạn muốn lấy trang thứ 3, mỗi trang 10 bản ghi, sắp xếp theo created_date giảm dần:

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

SELECT id, name, created_date FROM employees ORDER BY created_date DESC OFFSET (@PageNumber - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY; `

2. Sử dụng ROW_NUMBER() (SQL Server 2005 trở lên)#

Nếu bạn dùng phiên bản SQL Server cũ hơn hoặc muốn kiểm soát chi tiết hơn, có thể dùng hàm ROW_NUMBER():

WITH OrderedData AS ( SELECT column1, column2, ROW_NUMBER() OVER (ORDER BY column_sort) AS RowNum FROM table_name ) SELECT * FROM OrderedData WHERE RowNum BETWEEN @StartRow AND @EndRow;

• @StartRow = (@PageNumber - 1) * @PageSize + 1

• @EndRow = @PageNumber * @PageSize

Ví dụ:#

Lấy trang 2, mỗi trang 5 bản ghi, sắp xếp theo id tăng dần:

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

WITH OrderedEmployees AS ( SELECT id, name, ROW_NUMBER() OVER (ORDER BY id) AS RowNum FROM employees ) SELECT id, name FROM OrderedEmployees WHERE RowNum BETWEEN @StartRow AND @EndRow; `

Lưu ý khi viết truy vấn phân trang#

• Phải có ORDER BY để xác định thứ tự phân trang, nếu không dữ liệu sẽ không ổn định.

• Tránh sử dụng OFFSET quá lớn với bảng quá lớn vì có thể làm chậm truy vấn.

• Có thể kết hợp phân trang với các điều kiện lọc (WHERE) để tối ưu.

Dưới đây là ví dụ code Java và .NET (C#) để thực hiện truy vấn phân trang với SQL Server, dùng câu lệnh phân trang kiểu OFFSET...FETCH NEXT (SQL Server 2012+).

1. Ví dụ phân trang trong Java (JDBC)#

` import java.sql.*;

public class PagingExample {

public static void main(String[] args) {
    int pageNumber = 2;
    int pageSize = 10;

    String sql = "SELECT id, name, created_date FROM employees " +
                 "ORDER BY created_date DESC " +
                 "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";

    try (Connection conn = DriverManager.getConnection(
            "jdbc:sqlserver://localhost:1433;databaseName=YourDB", "username", "password");
         PreparedStatement pstmt = conn.prepareStatement(sql)) {

        int offset = (pageNumber - 1) * pageSize;
        pstmt.setInt(1, offset);
        pstmt.setInt(2, pageSize);

        ResultSet rs = pstmt.executeQuery();

        while (rs.next()) {
            System.out.println("ID: " + rs.getInt("id") +
                               ", Name: " + rs.getString("name") +
                               ", Created Date: " + rs.getDate("created_date"));
        }

    } catch (SQLException e) {
        e.printStackTrace();
    }
}

} `

2. Ví dụ phân trang trong C# (.NET) dùng ADO.NET#

` using System; using System.Data; using System.Data.SqlClient;

class PagingExample { static void Main() { int pageNumber = 2; int pageSize = 10;

    string connectionString = "Server=localhost;Database=YourDB;User Id=username;Password=password;";

    string sql = @"
        SELECT id, name, created_date
        FROM employees
        ORDER BY created_date DESC
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    int offset = (pageNumber - 1) * pageSize;

    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(sql, conn))
    {
        cmd.Parameters.Add("@Offset", SqlDbType.Int).Value = offset;
        cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;

        conn.Open();

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"ID: {reader.GetInt32(0)}, Name: {reader.GetString(1)}, Created Date: {reader.GetDateTime(2)}");
            }
        }
    }
}

} `

Giải thích chung:#

• pageNumber là trang muốn lấy (bắt đầu từ 1).

• pageSize là số bản ghi mỗi trang.

• OFFSET tính bằng (pageNumber - 1) * pageSize.