Вопрос по sql, performance, pagination, sql-server – Каков наилучший способ разбить результаты на страницы в SQL Server

396

Как лучше всего (с точки зрения производительности) разбивать результаты на страницы в SQL Server 2000, 2005, 2008, 2012, если вы также хотите получить общее количество результатов (до разбивки на страницы)?

Принятое решение не показывает, как оно является наилучшим (с точки зрения производительности). Любые данные, подтверждающие это на больших наборах данных? O.O
Я всегда задавался вопросом, почему они не поддерживают указание смещения как части TOP (например, поддержка MySQL / Posgresql с LIMIT / OFFSET). Например, они могли бы просто иметь синтаксис "SELECT TOP x, y ...." где x = количество строк, y = начальное смещение. Это также будет обратно совместимо. gregmac
@ O.O: Хороший тест можно найти здесь:4guysfromrolla.com/webtech/042606-1.shtml, Тем не менееseek method превзойдет любую смещенную нумерацию страниц. Lukas Eder
Привет, я тоже ... Реализация разбиения на страницы в 2005 году это действительно так неудобно ... opensas
@gregmac - Sql Server 2012 имеет ограничение / смещение сейчас. O.O

Ваш Ответ

16   ответов
2

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

Перед тестированием вы должны создать одну примерную таблицу и вставить в нее некоторую строку: (В реальном мире вы должны изменить предложение Where, учитывая ваши поля таблицы, и, возможно, у вас есть несколько соединений и подзапрос в основной части 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 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.

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
      ( -- We apply the filter logic here
        CASE
          WHEN @FilterType = 'None' THEN 1

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 1
            AND VLT.ID = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
            AND VLT.ID <> @FilterValue THEN 1               

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 2
            AND VLT.Name = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
            AND VLT.Name <> @FilterValue THEN 1         

         -- Tel column filter   
         WHEN @FilterType = 'Contain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 3
            AND VLT.Tel = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
            AND VLT.Tel <> @FilterValue 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 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.

;WITH
  Data_CTE
  AS
  (  
    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
      ( -- We apply the filter logic here
        CASE
          WHEN @FilterType = 'None' THEN 1

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 1
            AND VLT.ID = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
            AND VLT.ID <> @FilterValue THEN 1               

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 2
            AND VLT.Name = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
            AND VLT.Name <> @FilterValue THEN 1         

         -- Tel column filter   
         WHEN @FilterType = 'Contain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 3
            AND VLT.Tel = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
            AND VLT.Tel <> @FilterValue THEN 1    

        END
      ) = 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 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.

;WITH
  Data_CTE
  AS
  (  
    SELECT 
      *  
    FROM VLT
    WHERE
      ( -- We apply the filter logic here
        CASE
          WHEN @FilterType = 'None' THEN 1

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 1
            AND VLT.ID = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
            AND VLT.ID <> @FilterValue THEN 1               

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 2
            AND VLT.Name = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
            AND VLT.Name <> @FilterValue THEN 1         

         -- Tel column filter   
         WHEN @FilterType = 'Contain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 3
            AND VLT.Tel = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
            AND VLT.Tel <> @FilterValue THEN 1    

        END
      ) = 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,
    CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
        THEN Data.ID END DESC,
    CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
        THEN Data.Name END ASC,
    CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
        THEN Data.Name END ASC,
    CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
        THEN Data.Tel END ASC,
    CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
        THEN Data.Tel END ASC
OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;
0

ROW_NUMBER Если у вас нет сортировочного столбца, вы можете использоватьCURRENT_TIMESTAMP следующее:

SELECT TOP 20 
    col1,
    col2,
    col3,
    col4
FROM (
    SELECT 
         tbl.col1 AS col1
        ,tbl.col2 AS col2
        ,tbl.col3 AS col3
        ,tbl.col4 AS col4
        ,ROW_NUMBER() OVER (
            ORDER BY CURRENT_TIMESTAMP
            ) AS sort_row
    FROM dbo.MyTable tbl
    ) AS query
WHERE query.sort_row > 10
ORDER BY query.sort_row

меня это хорошо сработало при поиске по размерам таблиц даже до 700 000.

This fetches records 11 to 30.

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

используя переменную таблицы со столбцом IDENTITY:

DECLARE @pageNo int -- 1 based
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 (что пропускает использование индекса). Недостатком является количество использованного временного пространства, если набор данных очень большой и один находится рядом с последней страницей. Я не проверял производительность курсора в этом случае, но он мог бы быть лучше.

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

-18

ни драйвер, который вы используете. Поэтому я описываю это абстрактно.

Create a scrollable resultset / dataset. This required a primary on the table(s) jump to the end request the row count jump to the start of the page scroll through the rows until the end of the page
400

две разные операции. Для примера рассмотрим, что запрос, с которым вы работаете,

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 исходного запроса. Крутая вещь, особенно для веб-приложений, заключается в том, что вам не нужно сохранять какое-либо состояние, кроме номеров строк, которые должны быть возвращены.

Просто чтобы заметить, что ROW_NUMBER () не существует в SQL Server 2000
Microsoft добавила в SQL 2012 новую функцию, которая делает нумерацию страниц похожей на MySQL. Перейдите по этой ссылке, чтобы узнать, как. Это интересная статья:dbadiaries.com/…
возвращает ли это все строки из внутреннего запроса & amp; затем фильтр на основе внешнего запроса? Например: внутренний запрос возвращает 100 000 & amp; внешний запрос возвращает только 20.
хорошо, что, если вы дублируете во внутреннем выборе (например, когда у вас есть внутреннее соединение), как вы используете отдельный, потому что RowNumber отличается, и он не работает
@SoftwareGeek: воспринимайте его как подзапрос (внутренний запрос), возвращающий поток, который затем читается до тех пор, пока не будет выполнено внешнее предложение WHERE. Каким образом строки могут быть связаны с этим, полностью зависит от запроса, но оптимизатор обычно очень хорошо справляется с минимизацией этого числа. Использование средства просмотра графического плана выполнения в SQL Server Management Studio (используйте Запрос / Включить фактический план выполнения) очень полезно в этом отношении.
22

OFFSET а такжеFETCH NEXT Пункт для достижения нумерации страниц.

Попробуйте это для SQL Server:

In the SQL Server 2012 a new feature was added in the ORDER BY clause, to query optimization of a set data, making work easier with data paging for anyone who writes in T-SQL as well for the entire Execution Plan in SQL Server.

Below the T-SQL script with the same logic used in the previous example.

--CREATING A PAGING WITH OFFSET and FETCH clauses IN "SQL SERVER 2012"
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 10 
SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE
FROM TB_EXAMPLE
ORDER BY ID_EXAMPLE
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
FETCH NEXT @RowspPage ROWS ONLY;

TechNet. Пейджинг запросов с помощью SQL Server

0
create PROCEDURE SP_Company_List (@pagesize int = -1 ,@pageindex int= 0   ) > AS BEGIN  SET NOCOUNT ON;


    select  Id , NameEn     from Company  ORDER by Id ASC  
OFFSET (@pageindex-1 )* @pagesize   ROWS FETCH NEXt @pagesize ROWS ONLY END  GO
DECLARE   @return_value int

EXEC  @return_value = [dbo].[SP_Company_List]         @pagesize = 1 ,         > @pageindex = 2

SELECT    'Return Value' = @return_value

GO
3

ещение и выборку следующего в хранимой процедуре. OFFSET Keyword - Если мы используем смещение с предложением order by, тогда запрос пропустит количество записей, которое мы указали в OFFSET n Rows.

FETCH NEXT Keywords - Когда мы используем Fetch Next только с предложением order by, он возвращает количество строк, которые вы хотите отобразить в подкачке, без Offset, тогда SQL выдаст ошибку. Вот пример, приведенный ниже.

create procedure sp_paging
(
 @pageno as int,
 @records as int
)
as
begin
declare @offsetcount as int
set @offsetcount=(@pageno-1)*@records
select id,bs,variable from salary order by id offset @offsetcount rows fetch Next @records rows only
end

Вы можете выполнить это следующим образом.

exec sp_paging 2,3
14

MSDN: ROW_NUMBER (Transact-SQL)

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

The following example returns rows with numbers 50 to 60 inclusive in the order of the OrderDate.

WITH OrderedOrders AS
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY FirstName DESC) AS RowNumber, 
        FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
    FROM [dbo].[vSalesPerson]
) 
SELECT RowNumber, 
    FirstName, LastName, Sales YTD 
FROM OrderedOrders 
WHERE RowNumber > 50 AND RowNumber < 60;
  RowNumber FirstName    LastName               SalesYTD
  --- -----------  ---------------------- -----------------
  1   Linda        Mitchell               4251368.54
  2   Jae          Pak                    4116871.22
  3   Michael      Blythe                 3763178.17
  4   Jillian      Carson                 3189418.36
  5   Ranjit       Varkey Chudukatil      3121616.32
  6   José         Saraiva                2604540.71
  7   Shu          Ito                    2458535.61
  8   Tsvi         Reiter                 2315185.61
  9   Rachel       Valdez                 1827066.71
  10  Tete         Mensa-Annan            1576562.19
  11  David        Campbell               1573012.93
  12  Garrett      Vargas                 1453719.46
  13  Lynn         Tsoflias               1421810.92
  14  Pamela       Ansman-Wolfe           1352577.13
88

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

SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()
FROM players
WHERE (score < @previousScore)
   OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC
The "seek predicate"

@previousScore а также@previousPlayerId значения являются соответствующими значениями последней записи с предыдущей страницы. Это позволяет вам выбрать & quot; следующий & quot; стр. ЕслиORDER BY направлениеASC, просто используйте> вместо.

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

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

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

Total records before pagination

COUNT(*) OVER() Функция окна поможет вам подсчитать общее количество записей "до нумерации страниц". Если вы используете SQL Server 2000, вам придется прибегнуть к двум запросам дляCOUNT(*).

@ user960567: С точки зрения производительности, подкачка на клавиатуре всегда будет превосходить смещение, независимо от того, реализуете ли вы смещение под стандарт SQL.OFFSET .. FETCHили с предыдущимROW_NUMBER() трюки.
Статья о наборе ключейhere
Я должен согласиться с Хунто. Этот метод полностью исключает клиента с довольно стандартным пользовательским интерфейсом разбиения на страницы «Предыдущая 1 2 3 (4) 5 6 Следующая» где пользователи могут прыгать вперед. Это не совсем крайний случай в моем опыте ...
@Junto: подкачка клавиш не подходит для всех случаев. Это определенно не для сеток данных. Но это идеально подходит для таких сценариев, как бесконечная прокрутка страницы канала Facebook. Не имеет значения, добавляются ли новые посты вверху, ваши последующие посты будут правильно добавляться внизу, пока вы прокручиваете страницу вниз. Прекрасный пример использования для этого ... Такая вещь будет многоmuch сложнее реализовать с использованием предела смещения / выборки, используя только числа.
У меня есть три проблемы с методом поиска. [1] Пользователь не может перейти на страницу. [2] он предполагает последовательные ключи, то есть, если кто-то удаляет около 3 строк, я получаю страницу из 7 элементов вместо 10.RowNumber дает мне последовательно 10 пунктов на странице. [3] он не работает с существующими сетками, которые предполагаютpagenumber а такжеpagesize.
0

я использовал следующий пример запроса в моей базе данных SQL 2000, он хорошо работает и для SQL 2005. Мощность, которую он дает, динамически упорядочена с использованием нескольких столбцов. Я говорю вам ... это мощно :)

    ALTER PROCEDURE [dbo].[RE_ListingReports_SelectSummary] 

@CompanyID  int,
@pageNumber     int,
@pageSize   int, 
@sort       varchar(200)
AS

DECLARE @sql nvarchar(4000)
DECLARE @strPageSize nvarchar(20)
DECLARE @strSkippedRows nvarchar(20)
DECLARE @strFields nvarchar(4000)
DECLARE @strFilter nvarchar(4000)
DECLARE @sortBy nvarchar(4000)
DECLARE @strFrom nvarchar(4000)
DECLARE @strID nvarchar(100)

If(@pageNumber < 0)
  SET @pageNumber = 1
SET @strPageSize = CAST(@pageSize AS varchar(20)) 
SET @strSkippedRows = CAST(((@pageNumber - 1) * @pageSize) AS varchar(20))-- For    example if pageNumber is 5  pageSize is 10, then SkippedRows = 40.
SET @strID = 'ListingDbID'
SET @strFields = 'ListingDbID,
ListingID,  
[ExtraRoom]
'
SET @strFrom = ' vwListingSummary '

SET @strFilter = ' WHERE
        CompanyID = ' + CAST(@CompanyID As varchar(20)) 
End
SET @sortBy = ''
if(len(ltrim(rtrim(@sort))) > 0)
SET @sortBy = ' Order By ' + @sort

-- Total Rows Count

SET @sql =  'SELECT Count(' + @strID + ')  FROM ' + @strFROM + @strFilter
EXEC sp_executesql @sql

--// This technique is used in a Single Table pagination
SET @sql = 'SELECT ' + @strFields + ' FROM ' + @strFROM +
    ' WHERE ' + @strID +  ' IN ' + 
   '  (SELECT TOP ' + @strPageSize + ' ' + @strID + ' FROM ' + @strFROM + @strFilter + 
             ' AND  ' + @strID + ' NOT IN ' + '
          (SELECT TOP ' + @strSkippedRows + ' ' + @strID + ' FROM ' + @strFROM + @strFilter + @SortBy + ') ' 
   + @SortBy + ') ' + @SortBy
Print @sql 
EXEC sp_executesql @sql

Лучшая часть - sp_executesql кеширует последующие вызовы, при условии, что вы передаете те же параметры, т.е. генерируете тот же текст SQL.

1

стрыми, просто установите номер страницы.

use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6;
with result as(
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
)
select SalesOrderDetailID, SalesOrderID, ProductID from result
WHERE result.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)

также с CTE

use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM (
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
 ) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
0
   CREATE view vw_sppb_part_listsource as 
    select row_number() over (partition by sppb_part.init_id order by sppb_part.sppb_part_id asc ) as idx, * from (
      select 
          part.SPPB_PART_ID
          , 0 as is_rev
          , part.part_number 
          , part.init_id 
      from t_sppb_init_part part 
      left join t_sppb_init_partrev prev on ( part.SPPB_PART_ID = prev.SPPB_PART_ID )
      where prev.SPPB_PART_ID is null 
      union 
      select 
          part.SPPB_PART_ID
          , 1 as is_rev
          , prev.part_number 
          , part.init_id 
      from t_sppb_init_part part 
      inner join t_sppb_init_partrev prev on ( part.SPPB_PART_ID = prev.SPPB_PART_ID )
    ) sppb_part

389

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

Для получения следующих 10 строк просто запустите этот запрос:

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

http://technet.microsoft.com/en-us/library/gg699618.aspx

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

ORDER BY is mandatory to use OFFSET and FETCH clause. OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH. TOP cannot be combined with OFFSET and FETCH in the same query expression.
@ RichardMarskell-Drackir Там много проблем сFOR XML PATH (''), Во-первых, он заменяет управляющие символы XML на коды сущностей XML. Надеюсь, у вас нет<, >, или же& в ваших данных! Во-вторых,FOR XML PATH ('') используемый таким образом фактически недокументированный синтаксис. Вы должны указать именованный столбец или альтернативное имя элемента. Делать ни то, ни другое в документе не означает, что поведение ненадежно. В-третьих, чем больше мы принимаем сломанноеFOR XML PATH ('') синтаксис, тем менее вероятно, что MS на самом деле обеспечиваетreal LISTAGG() [ OVER() ] функционировать, как они должны были.
Все еще ждуLISTAGG()/GROUP_CONCAT().
@Jon, этот связанный пост в блоге не является репрезентативным, в том смысле, что он делает сравнения на основе возврата результата страницы путем поиска значений столбца id.
позор перф так плохоmssqlgirl.com/…
@BaconBits Смотрите этот ответ для хитрого способа сделать это сFOR XML: stackoverflow.com/a/273330/429949
1

SELECT TOP @offset a.*
FROM (select top @limit b.*, COUNT(*) OVER() totalrows 
        from TABLENAME b order by id asc) a
ORDER BY id desc;
15

http://www.codeproject.com/KB/aspnet/PagingLarge.aspx

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

Интересно, что в этой статье не упоминаетсяseek method, который умеет выполнять пейджинг в постоянное время ... Еще хорошая статья

Похожие вопросы