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