Методы подкачки SQL Server 2008?

Следующая хранимая процедура T-SQL является очень эффективной реализацией разбиения на страницы. Оптимизатор SQL может очень быстро найти первый идентификатор. Объедините это с использованием ROWCOUNT, и вы получите подход, который эффективен как для ЦП, так и для чтения. Для таблицы с большим количеством строк это, безусловно, превосходит любой подход, который я видел с использованием временной таблицы или табличной переменной.

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

РЕДАКТИРОВАТЬ: если вы сортируете столбец с потенциально неуникальными значениями (например, LastName), добавьте второй столбец в предложение Order By, чтобы снова сделать значения сортировки уникальными.

CREATE  PROCEDURE dbo.PagingTest
(
    @PageNumber int,
    @PageSize int
)
AS

DECLARE @FirstId int, @FirstRow int

SET @FirstRow = ( (@PageNumber - 1) * @PageSize ) + 1
SET ROWCOUNT @FirstRow

-- Add check here to ensure that @FirstRow is not
-- greater than the number of rows in the table.

SELECT   @FirstId = [Id]
FROM     dbo.TestTable
ORDER BY [Id]

SET ROWCOUNT @PageSize

SELECT   *
FROM     dbo.TestTable
WHERE    [Id] >= @FirstId
ORDER BY [Id]

SET ROWCOUNT 0
GO 

Если вы используете CTE с двумя столбцами row_number () - один отсортированный по возрастанию и один по убыванию, вы получите номера строк для разбиения по страницам, а также общее количество записей, добавив два столбца row_number.

create procedure get_pages(@page_number int, @page_length int)
as
    set nocount on;

    with cte as
    (
        select 
            Row_Number() over (order by sort_column desc) as row_num
            ,Row_Number() over (order by sort_column) as inverse_row_num
            ,id as cte_id
        From my_table
    )
    Select 
        row_num+inverse_row_num as total_rows
        ,*  
    from CTE inner join my_table
        on cte_id=df_messages.id
    where row_num between 
        (@page_number)*@page_length 
        and (@page_number+1)*@page_length
    order by rownumber

Использование смещения

Другие объяснили, как функцию ранжирования ROW_NUMBER() OVER() можно использовать для выполнения страниц. Стоит отметить, что SQL Server 2012 наконец-то включил поддержку стандарта SQL _2 _ пункт:

SELECT first_name, last_name, score
FROM players
ORDER BY score DESC
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY

Если вы используете SQL Server 2012 и обратная совместимость не является проблемой, вам, вероятно, следует предпочесть этот пункт, поскольку он будет выполняться SQL Server более оптимально в крайних случаях.

Использование метода SEEK

Существует совершенно другой, гораздо более быстрый способ выполнения подкачки в SQL. Это часто называют «методом поиска», как описано в это сообщение в блоге здесь.

SELECT TOP 10 first_name, last_name, score
FROM players
WHERE (score < @previousScore)
   OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC

Значения @previousScore и @previousPlayerId являются соответствующими значениями последней записи с предыдущей страницы. Это позволяет вам перейти на «следующую» страницу. Если ORDER BY направление ASC, просто используйте вместо этого >.

С помощью описанного выше метода вы не можете сразу перейти на страницу 4, не загрузив сначала предыдущие 40 записей. Но зачастую вы все равно не хотите прыгать так далеко. Вместо этого вы получаете гораздо более быстрый запрос, который может извлекать данные за постоянное время, в зависимости от вашей индексации. Кроме того, ваши страницы остаются «стабильными» независимо от того, изменяются ли базовые данные (например, на странице 1, пока вы находитесь на странице 4).

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

Обратите внимание, что «метод поиска» также называется разбиением по набору клавиш.

Попробуйте что-то вроде этого:

declare @page int = 2
declare @size int = 10

declare @lower int =  (@page - 1) * @size
declare @upper int =  (@page    ) * @size

select * from (
select 
    ROW_NUMBER() over (order by some_column) lfd,
* from your_table
) as t
 where lfd between @lower and @upper
 order by some_column

Вот обновленная версия кода @ RoadWarrior с использованием TOP. Производительность такая же, и очень быстрая. Убедитесь, что у вас есть индекс на TestTable.ID

CREATE PROC dbo.PagingTest
    @SkipRows int,
    @GetRows int
AS
DECLARE @FirstId int

SELECT   TOP (@SkipRows) 
         @FirstId = [Id]
FROM     dbo.TestTable
ORDER BY [Id]

SELECT   TOP (@GetRows) *
FROM     dbo.TestTable
WHERE    [Id] >= @FirstId
ORDER BY [Id]

GO 

Попробуй это

Declare @RowStart int, @RowEnd int;


SET @RowStart = 4;
SET @RowEnd = 7; 

With MessageEntities As 
(
    Select ROW_NUMBER() Over (Order By [MESSAGE_ID]) As Row, [MESSAGE_ID]
    From [TBL_NAFETHAH_MESSAGES]
)
Select  m0.MESSAGE_ID, m0.MESSAGE_SENDER_NAME,
        m0.MESSAGE_SUBJECT, m0.MESSAGE_TEXT
From MessageEntities M
    Inner Join [TBL_NAFETHAH_MESSAGES] m0 on M.MESSAGE_ID = m0.MESSAGE_ID
Where M.Row Between @RowStart AND @RowEnd
Order By M.Row Asc
GO

Почему бы не использовать рекомендованный решение:

ВЫБЕРИТЕ СТОИМОСТЬ продукта ИЗ AdventureWorksEntities.Products КАК продукт упорядочить по продукту.ListPrice ПРОПУСТИТЬ @ skip LIMIT @limit