paginacion - sql server 2008 pagination

Cómo hacer paginación en SQL Server 2008

Question

¿Cómo se hace la paginación en SQL Server 2008?

Answer 1

Estas son mi solución para paginar el resultado de la consulta en el lado del servidor SQL. He agregado el concepto de filtrado y orden con una columna. Es muy eficiente cuando busca y filtra y ordena en su Gridview.

Antes de realizar la prueba, debe crear una tabla de muestra e insertar una fila en esta tabla: (en el mundo real, debe cambiar la cláusula Where considerando su campo de tabla y quizás tenga alguna combinación y subconsulta en la parte principal de 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

En SQL Server 2008, puede usar el concepto CTE. Por eso, he escrito dos tipos de consultas para 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

Y una segunda solución con CTE en el servidor SQL 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

Answer 2

Otra solución que funciona desde SQL 2005 al menos, es utilizar TOP con SELECT subconsultas y cláusulas ORDER BY.

En resumen, recuperar las filas de la página 2 con 10 filas por página es lo mismo que recuperar las últimas 10 filas de las primeras 20 filas. Lo que se traduce en recuperar las primeras 20 filas con orden ASC, y luego las primeras 10 filas con orden DESC, antes de ordenar nuevamente usando ASC.

Ejemplo: recuperación de la página 2 filas con 3 filas por página

create table test(id integer);
insert into test values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

select * 
    from (
        select top 2 * 
            from (
                select  top (4) * 
                    from test 
                    order by id asc) tmp1
            order by id desc) tmp1 
    order by id asc

Answer 3

Puede usar ROW_NUMBER() :

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.

Ejemplo:

WITH CTEResults AS
(
    SELECT IDColumn, SomeField, DateField, ROW_NUMBER() OVER (ORDER BY DateField) AS RowNum
    FROM MyTable
)

SELECT * 
FROM CTEResults
WHERE RowNum BETWEEN 10 AND 20;

Answer 4

Puedes intentar algo como

DECLARE @Table TABLE(
        Val VARCHAR(50)
)

DECLARE @PageSize INT,
        @Page INT

SELECT  @PageSize = 10,
        @Page = 2

;WITH PageNumbers AS(
        SELECT Val,
                ROW_NUMBER() OVER(ORDER BY Val) ID
        FROM    @Table
)
SELECT  *
FROM    PageNumbers
WHERE   ID  BETWEEN ((@Page - 1) * @PageSize + 1)
        AND (@Page * @PageSize)

Answer 5

SELECT DISTINCT Id, ParticipantId, ActivityDate, IsApproved, IsDeclined, IsDeleted, SubmissionDate, IsResubmitted,

    [CategoryId] Id,[CategoryName] Name,

    [ActivityId] [Id],[ActivityName] Name,Points,   

    [UserId] [Id],Email,
    ROW_NUMBER() OVER(ORDER BY Id desc)   AS RowNum from
    (SELECT DISTINCT
    Id,ParticipantId,
    ActivityDate,IsApproved,
    IsDeclined,IsDeleted,
    SubmissionDate, IsResubmitted,  

    [CategoryId] [CategoryId],[CategoryName] [CategoryName],

    [ActivityId] [ActivityId],[ActivityName] [ActivityName],Points, 

    [UserId] [UserId],Email,
    ROW_NUMBER() OVER(ORDER BY Id desc)   AS RowNum from

     (SELECT DISTINCT ASN.Id,
    ASN.ParticipantId,ASN.ActivityDate,
    ASN.IsApproved,ASN.IsDeclined,
    ASN.IsDeleted,ASN.SubmissionDate,
    CASE WHEN (SELECT COUNT(*) FROM FDS_ActivitySubmission WHERE ParentId=ASN.Id)>0 THEN CONVERT(BIT, 1) ELSE CONVERT(BIT, 0) END IsResubmitted,

    AC.Id [CategoryId], AC.Name [CategoryName],

    A.Id [ActivityId],A.Name [ActivityName],A.Points,

    U.Id[UserId],U.Email    


FROM
FDS_ActivitySubmission ASN WITH (NOLOCK)
INNER JOIN  
    FDS_ActivityCategory AC WITH (NOLOCK)
ON 
    AC.Id=ASN.ActivityCategoryId
        INNER JOIN
    FDS_ApproverDetails FDSA
ON
FDSA.ParticipantID=ASN.ParticipantID

        INNER JOIN
       FDS_ActivityJobRole FAJ

EN FAJ.RoleId = FDSA.JobRoleId INNER JOIN

    FDS_Activity A WITH (NOLOCK)
ON 
    A.Id=ASN.ActivityId
INNER JOIN
   Users U WITH (NOLOCK)
ON
    ASN.ParticipantId=FDSA.ParticipantID
WHERE
       IsDeclined=@IsDeclined AND IsApproved=@IsApproved    AND ASN.IsDeleted=0
       AND
       ISNULL(U.Id,0)=ISNULL(@ApproverId,0)
       AND ISNULL(ASN.IsDeleted,0)<>1)P)t where t.RowNum between 
       (((@PageNumber - 1) * @PageSize) + 1) AND (@PageNumber * PageSize)
    AND t.IsDeclined=@IsDeclined AND t.IsApproved=@IsApproved AND t.IsDeleted = 0
 AND (ISNULL(t.Id,0)=ISNULL(@SubmissionId,0)or ISNULL(@SubmissionId,0)<=0) 

Answer 6

SQL Server 2012 proporciona funcionalidad de paginación (vea http://www.codeproject.com/Articles/442503/New-features-for-database-developers-in-SQL-Server )

En SQL2008 puedes hacerlo de esta manera:

declare @rowsPerPage as bigint; 
declare @pageNum as bigint; 
set @rowsPerPage=25; 
set @pageNum=10;   

With SQLPaging As   ( 
    Select Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY ID asc) 
    as resultNum, * 
    FROM Employee )
select * from SQLPaging with (nolock) where resultNum > ((@pageNum - 1) * @rowsPerPage)

Prooven! Funciona y escala consistentemente.

Answer 7

1) CREAR DUMMY DATA

CREATE TABLE #employee (EMPID INT IDENTITY, NAME VARCHAR(20))

DECLARE @id INT = 1

WHILE @id < 200

BEGIN
INSERT INTO #employee ( NAME ) VALUES (''employee_'' + CAST(@id AS VARCHAR) )
SET @id = @id + 1
END

2) AHORA APLICA LA SOLUCIÓN.

Este caso supone que EMPID es una columna única y ordenada.

Fuera del curso, lo aplicará en una columna diferente ...

DECLARE @pageSize INT = 20

SELECT * FROM (

SELECT *, PageNumber =  CEILING(CAST(EMPID AS FLOAT)/@pageSize)   
FROM #employee
) MyQuery

WHERE MyQuery.PageNumber = 1