Какой самый эффективный способ страницировать результаты в SQL Server?

Наконец-то выпущен Microsoft SQL Server 2012, и мне очень нравится его простота для пагинации, вам не нужно использовать сложные запросы, как это обсуждается в других ответах.

Чтобы получить следующие 10 строк, просто выполните следующий запрос:

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Дополнительная информация доступна по ссылке: документация Microsoft.

Ключевые моменты, которые следует учитывать при использовании этого подхода:

• ORDER BY является обязательным для использования с предложением OFFSET ... FETCH.
• Предложение OFFSET обязательно должно использоваться с FETCH. Нельзя использовать ORDER BY ... FETCH.
• TOP не может быть объединен с OFFSET и FETCH в одном выражении запроса.

Получение общего числа результатов и пагинация — это две разные операции. Для примера предположим, что ваш запрос выглядит следующим образом:

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

В этом случае, чтобы определить общее число результатов, вы можете использовать следующий запрос:

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

Это может показаться неэффективным, но на самом деле это довольно производительно, если все индексы и т.д. настроены правильно.

Теперь, чтобы получить фактические результаты с пагинацией, наиболее эффективным будет следующий запрос:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Этот запрос вернет строки с 1 по 19 оригинального запроса. Примечательно, особенно для веб-приложений, что вам не нужно сохранять никакое состояние, кроме номеров строк, которые необходимо вернуть.

Существует хороший обзор различных техник постраничной навигации по следующей ссылке: http://www.codeproject.com/KB/aspnet/PagingLarge.aspx.

Я часто использовал метод ROWCOUNT, в основном с SQL Server 2000 (он также работает с 2005 и 2008, просто сравните производительность с ROW_NUMBER), он очень быстрый, но вам нужно убедиться, что сортируемые столбцы имеют (в основном) уникальные значения.

В SQL Server 2000 вы можете имитировать функцию ROW_NUMBER() с помощью переменной таблицы с колонкой IDENTITY:

DECLARE @pageNo int -- нумерация страниц начинается с 1
DECLARE @pageSize int
SET @pageNo = 51
SET @pageSize = 20

DECLARE @firstRecord int
DECLARE @lastRecord int
SET @firstRecord = (@pageNo - 1) * @pageSize + 1 -- 1001
SET @lastRecord = @firstRecord + @pageSize - 1   -- 1020

DECLARE @orderedKeys TABLE (
  rownum int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
  TableKey int NOT NULL
)

SET ROWCOUNT @lastRecord
INSERT INTO @orderedKeys (TableKey) 
SELECT ID FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

SET ROWCOUNT 0

SELECT t.*
FROM Orders t
  INNER JOIN @orderedKeys o ON o.TableKey = t.ID
WHERE o.rownum >= @firstRecord
ORDER BY o.rownum

Этот подход можно расширить для таблиц с многоколонными ключами, и он не влечет за собой дополнительные затраты на производительность, связанные с использованием оператора OR (который пропускает использование индекса). Однако следует учитывать, что при работе с очень большими наборами данных и при нахождении близко к последней странице, данный метод может потреблять значительное количество временного пространства. Я не тестировал производительность курсоров в таком случае, но они могут работать лучше.

Обратите внимание, что этот подход можно оптимизировать для первой страницы данных. Также стоит отметить, что в SQL Server 2000 использовался ROWCOUNT, так как оператор TOP не принимает переменные.

Вот мои решения для постраничного вывода результатов запросов на стороне SQL Server. Эти подходы различаются между версиями SQL Server 2008 и 2012. Я также добавил концепцию фильтрации и сортировки по одному полю, что очень эффективно при постраничном выводе и фильтрации в вашем GridView.

Перед тестированием вам нужно создать пример таблицы и вставить в неё несколько строк. (В реальном мире вам нужно будет изменить условие WHERE, учитывая поля вашей таблицы, и, возможно, у вас будут JOIN и подзапросы в основной части запроса SELECT)

Create Table VLT
(
    ID int IDentity(1,1),
    Name nvarchar(50),
    Tel Varchar(20)
)
GO

Insert INTO VLT
VALUES
    ('NAME' + Convert(varchar(10),@@identity),'FAMIL' + Convert(varchar(10),@@identity))
GO 500000

Во всех этих примерах я хочу запросить 200 строк на страницу и получить строки для страницы номер 1200.

Для SQL Server 2008 вы можете воспользоваться концепцией CTE. Поэтому я написал два типа запросов для SQL Server 2008 и более поздних версий.

SQL Server 2008+

DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 -- Поле, по которому будет произведена сортировка
DECLARE @SortOrder nvarchar(255) = 'ASC' -- ASC или DESC
DECLARE @FilterType nvarchar(255) = 'None' -- Тип фильтра, как определено на стороне клиента (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' -- Значение, которое пользователь ввел для фильтра
DECLARE @FilterColumn int = 1 -- Столбец, к которому применяется фильтр, представляет номер столбца, как когда мы отправляем информацию.

SELECT 
  Data.ID,
  Data.Name,
  Data.Tel
FROM
  (  
    SELECT 
      ROW_NUMBER() 
        OVER( ORDER BY 
                CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
                      THEN VLT.ID END ASC,
                CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
                      THEN VLT.ID END DESC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
                      THEN VLT.Tel END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
                      THEN VLT.Tel END ASC
         ) AS RowNum
      ,*  
    FROM VLT 
    WHERE
      ( -- Здесь мы применяем логику фильтрации
        CASE
          WHEN @FilterType = 'None' THEN 1
          ...
        END
      ) = 1   
  ) AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
  AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum

GO

А это второе решение с использованием CTE в SQL Server 2008 и более поздних версиях:

DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 -- Поле, по которому будет произведена сортировка
DECLARE @SortOrder nvarchar(255) = 'ASC' -- ASC или DESC
DECLARE @FilterType nvarchar(255) = 'None' -- Тип фильтра, как определено на стороне клиента (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' -- Значение, которое пользователь ввел для фильтра
DECLARE @FilterColumn int = 1 -- Столбец, к которому применяется фильтр, представляет номер столбца, как когда мы отправляем информацию.

;WITH Data_CTE AS  
(  
    SELECT 
      ROW_NUMBER() 
        OVER( ORDER BY 
                ...
         ) AS RowNum
      ,*  
    FROM VLT
    WHERE
      ( ... ) = 1     
) 

SELECT 
  Data.ID,
  Data.Name,
  Data.Tel
FROM Data_CTE AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
  AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum

SQL Server 2012+

DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 -- Поле, по которому будет произведена сортировка
DECLARE @SortOrder nvarchar(255) = 'ASC' -- ASC или DESC
DECLARE @FilterType nvarchar(255) = 'None' -- Тип фильтра, как определено на стороне клиента (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' -- Значение, которое пользователь ввел для фильтра
DECLARE @FilterColumn int = 1 -- Столбец, к которому применяется фильтр, представляет номер столбца, как когда мы отправляем информацию.

;WITH Data_CTE AS  
(  
    SELECT *  
    FROM VLT
    WHERE ( ... ) = 1         
)

SELECT 
  Data.ID,
  Data.Name,
  Data.Tel
FROM Data_CTE AS Data
ORDER BY 
    CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
        THEN Data.ID END ASC,
    ...
OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;

Эти запросы помогут вам эффективно реализовать постраничный вывод с учетом фильтрации и сортировки.