В этой статье блога мы погрузимся в мир нумерации страниц в хранимых процедурах SQL Server. Пагинация позволяет разбивать большие наборы результатов на управляемые фрагменты, что упрощает отображение данных в удобной для пользователя форме. Мы рассмотрим различные методы разбиения на страницы, уделив особое внимание использованию предложений LIMIT и OFFSET, а также приведем несколько примеров кода, иллюстрирующих каждый подход.

Метод 1: использование OFFSET и FETCH
Предложения OFFSET и FETCH были представлены в SQL Server 2012, обеспечивая удобный способ разбиения на страницы. Предложение OFFSET указывает количество пропущенных строк, а предложение FETCH определяет количество возвращаемых строк.

CREATE PROCEDURE GetEmployees
    @PageNumber INT,
    @PageSize INT
AS
BEGIN
    DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;

    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    ORDER BY EmployeeID
    OFFSET @Offset ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END

Метод 2: использование функции ROW_NUMBER()
Другой подход заключается в использовании функции ROW_NUMBER() для присвоения последовательного номера каждой строке в наборе результатов. Затем вы можете фильтровать строки по присвоенному номеру строки.

CREATE PROCEDURE GetEmployees
    @PageNumber INT,
    @PageSize INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName
    FROM (
        SELECT EmployeeID, FirstName, LastName,
               ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum
        FROM Employees
    ) AS EmployeeData
    WHERE RowNum BETWEEN ((@PageNumber - 1) * @PageSize) + 1
                     AND (@PageNumber * @PageSize);
END

Метод 3: использование общих табличных выражений (CTE)
CTE предоставляют краткий способ определения временных наборов результатов в хранимой процедуре. Объединив CTE с функцией ROW_NUMBER(), вы можете добиться нумерации страниц.

CREATE PROCEDURE GetEmployees
    @PageNumber INT,
    @PageSize INT
AS
BEGIN
    WITH CTE AS (
        SELECT EmployeeID, FirstName, LastName,
               ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum
        FROM Employees
    )
    SELECT EmployeeID, FirstName, LastName
    FROM CTE
    WHERE RowNum BETWEEN ((@PageNumber - 1) * @PageSize) + 1
                     AND (@PageNumber * @PageSize);
END

Метод 4. Использование временных таблиц
В сценариях, где задействованы сложные вычисления или множественные соединения, использование временной таблицы может быть эффективным подходом к разбиению на страницы.

CREATE PROCEDURE GetEmployees
    @PageNumber INT,
    @PageSize INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName,
           ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum
    INTO #TempEmployees
    FROM Employees;

    SELECT EmployeeID, FirstName, LastName
    FROM #TempEmployees
    WHERE RowNum BETWEEN ((@PageNumber - 1) * @PageSize) + 1
                     AND (@PageNumber * @PageSize);

    DROP TABLE #TempEmployees;
END

В этой статье мы рассмотрели несколько методов разбиения на страницы в хранимых процедурах SQL Server. Мы рассмотрели использование предложений LIMIT и OFFSET, функции ROW_NUMBER(), общих табличных выражений (CTE) и временных таблиц. Каждый метод имеет свои сильные стороны и может быть более подходящим в зависимости от конкретных требований вашего приложения. Освоив эти методы нумерации страниц, вы сможете эффективно обрабатывать большие наборы данных и повышать удобство работы пользователей в приложениях SQL Server.