paginar - sql server pagination

¿Cuál es la mejor manera de paginar los resultados en SQL Server?

Question

¿Cuál es la mejor manera (en cuanto al rendimiento) de paginar los resultados en SQL Server 2000, 2005, 2008, 2012 si también desea obtener el número total de resultados (antes de la paginación)?

Answer 1

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

Answer 2

Bueno, he usado la siguiente consulta de muestra en mi base de datos SQL 2000, también funciona bien para SQL 2005. El poder que le da es dinámicamente orden mediante el uso de varias columnas. Te digo ... esto es poderoso :)

    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

La mejor parte es sp_executesql almacena en caché las llamadas posteriores, siempre que pase los mismos parámetros, es decir, genere el mismo texto de SQL.

Answer 3

Desde SQL Server 2012, podemos usar la cláusula OFFSET y FETCH NEXT para lograr la paginación.

Intenta esto, para SQL Server:

En el SQL Server 2012 se agregó una nueva característica en la cláusula ORDER BY, para consultar la optimización de un conjunto de datos, facilitando el trabajo con la paginación de datos para cualquiera que escriba en T-SQL y para todo el Plan de Ejecución en SQL Server.

Debajo de la secuencia de comandos T-SQL con la misma lógica utilizada en el ejemplo anterior.

--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: paginando una consulta con SQL Server

Answer 4

El uso de mayúsculas y minúsculas parece ser fácil de usar y rápido, simplemente configure el número de página.

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)

tambien con 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)

Answer 5

Estas son mis soluciones para paginar el resultado de la consulta en el lado del servidor SQL. estos enfoques son diferentes entre SQL Server 2008 y 2012. Además, he agregado el concepto de filtrado y orden con una columna. Es muy eficiente cuando está paginando y filtrando y ordenando en su Gridview.

Antes de realizar la prueba, debe crear una tabla de muestra e insertar alguna fila en esta tabla: (En el mundo real, tiene que cambiar la cláusula Where considerando sus campos de tabla y tal vez tenga alguna combinación y subconsulta en la parte principal de la selección)

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

En todas estas muestras, quiero consultar 200 filas por página y estoy buscando la fila para el número de página 1200.

En SQL Server 2008, puede utilizar el concepto CTE. Debido a eso, he escrito dos tipos de consulta para el servidor SQL 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

Y segunda solución con CTE en 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;

Answer 6

Finalmente, se lanzó Microsoft SQL Server 2012 , realmente me gusta su simplicidad para una paginación, no tiene que usar consultas complejas como las que se responden aquí.

Para obtener las siguientes 10 filas simplemente ejecuta esta consulta:

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

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

Puntos clave a considerar cuando se usa:

• ORDER BY es obligatorio para usar la cláusula OFFSET y FETCH.
• La cláusula OFFSET es obligatoria con FETCH. Nunca puedes usar, ORDER BY ... FETCH.
• TOP no se puede combinar con OFFSET y FETCH en la misma expresión de consulta.

Answer 7

Hay una buena descripción general de las diferentes técnicas de paginación en http://www.codeproject.com/KB/aspnet/PagingLarge.aspx

He usado el método ROWCOUNT muy a menudo principalmente con SQL Server 2000 (también funcionará con 2005 y 2008, solo mide el rendimiento en comparación con ROW_NUMBER), es muy rápido, pero debe asegurarse de que las columnas ordenadas tienen (en su mayoría ) valores únicos.

Answer 8

Increíblemente, ninguna otra respuesta ha mencionado la forma más rápida de realizar la paginación en todas las versiones de SQL Server. Las compensaciones pueden ser terriblemente lentas para números de página grandes, como se 4guysfromrolla.com/webtech/042606-1.shtml . Existe una forma completamente diferente y mucho más rápida de realizar la paginación en SQL. Esto se suele denominar "método de búsqueda" o "paginación de conjunto de claves" como se describe en esta publicación del blog aquí

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

El "buscar predicado"

Los valores @previousScore y @previousPlayerId son los valores respectivos del último registro de la página anterior. Esto le permite obtener la página "siguiente". Si la dirección ORDER BY es ASC , simplemente use > lugar.

Con el método anterior, no puede pasar inmediatamente a la página 4 sin haber obtenido antes los 40 registros anteriores. Pero a menudo, no quieres saltar tan lejos de todos modos. En su lugar, obtienes una consulta mucho más rápida que podría recuperar datos en un tiempo constante, dependiendo de tu indexación. Además, sus páginas permanecen "estables", sin importar si los datos subyacentes cambian (por ejemplo, en la página 1, mientras que usted está en la página 4).

Esta es la mejor manera de implementar la paginación cuando se cargan más datos en aplicaciones web, por ejemplo.

Tenga en cuenta que el "método de búsqueda" también se llama paginación de conjunto de claves .

Total de registros antes de la paginación

La función de ventana COUNT(*) OVER() le ayudará a contar el número de registros totales "antes de la paginación". Si está utilizando SQL Server 2000, tendrá que recurrir a dos consultas para COUNT(*) .

Answer 9

La mejor forma de paginar en el servidor SQL 2012 es usando offset y fetch en un procedimiento almacenado. Palabra clave OFFSET : si usamos la compensación con la cláusula order by, la consulta omitirá el número de registros que especificamos en OFFSET n Rows.

Palabras clave de FETCH NEXT : cuando usamos Fetch Next con una cláusula order by solo devolverá el número de filas que desea mostrar en la paginación, sin Offset, entonces SQL generará un error. Aquí está el ejemplo dado abajo.

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

Puedes ejecutarlo como sigue.

exec sp_paging 2,3

Answer 10

No ha especificado el idioma ni el controlador que está utilizando. Por lo tanto lo estoy describiendo abstractamente.

• Crear un conjunto de datos / conjunto de datos desplazable. Esto requería una primaria en la (s) mesa (s)
• saltar al final
• solicitar el recuento de filas
• saltar al inicio de la página
• desplazarse por las filas hasta el final de la página

Answer 11

Para SQL Server 2000 puede simular ROW_NUMBER () usando una variable de tabla con una columna IDENTIDAD:

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

Este enfoque puede extenderse a las tablas con claves de varias columnas, y no incurre en la sobrecarga de rendimiento del uso de OR (que omite el uso del índice). El inconveniente es la cantidad de espacio temporal utilizado si el conjunto de datos es muy grande y uno está cerca de la última página. No probé el rendimiento del cursor en ese caso, pero podría ser mejor.

Tenga en cuenta que este enfoque podría optimizarse para la primera página de datos. Además, se utilizó ROWCOUNT ya que TOP no acepta una variable en SQL Server 2000.

Answer 12

Para la técnica ROW_NUMBER , si no tiene una columna de clasificación para usar, puede utilizar CURRENT_TIMESTAMP siguiente manera:

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

Esto me ha funcionado bien para búsquedas en tamaños de tablas de hasta 700,000.

Esto recoge registros de 11 a 30.

Answer 13

Pruebe este enfoque:

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

Answer 14

MSDN: ROW_NUMBER (Transact-SQL)

Devuelve el número secuencial de una fila dentro de una partición de un conjunto de resultados, comenzando en 1 para la primera fila en cada partición.

El siguiente ejemplo devuelve filas con los números 50 a 60 inclusive en el orden de 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

Answer 15

Obtener el número total de resultados y paginar son dos operaciones diferentes. Por el bien de este ejemplo, supongamos que la consulta con la que está tratando es

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

En este caso, determinaría el número total de resultados utilizando:

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

... lo que puede parecer ineficiente, pero en realidad es bastante eficaz, asumiendo que todos los índices, etc. están configurados correctamente.

A continuación, para obtener los resultados reales de manera paginada, la siguiente consulta sería la más eficiente:

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

Esto devolverá las filas 1-19 de la consulta original. Lo interesante aquí, especialmente para las aplicaciones web, es que no tiene que mantener ningún estado, excepto los números de fila que se devolverán.

Answer 16

   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

reiniciará idx cuando se trata de diferentes init_id