SQL Server에서 결과를 페이지로 이동하는 가장 좋은 방법은 무엇입니까?

code

SQL Server에서 결과를 페이지로 이동하는 가장 좋은 방법은 무엇입니까?

starcafe 2023. 5. 3. 21:35

SQL Server에서 결과를 페이지로 이동하는 가장 좋은 방법은 무엇입니까?

총 결과 수(페이징 전)를 얻으려면 SQL Server 2000, 2005, 2008, 2012에서 결과를 페이징하는 가장 좋은 방법은 무엇입니까?

마지막으로 마이크로소프트 SQL 서버 2012가 출시되었습니다. 페이지 구성의 단순성이 정말 마음에 듭니다. 여기서 답변한 것처럼 복잡한 쿼리를 사용할 필요가 없습니다.

다음 10개 행을 가져오려면 다음 쿼리를 실행하십시오.

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

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql#using-offset-and-fetch-to-limit-the-rows-returned

사용 시 고려해야 할 핵심 사항:

ORDER BY사용하려면 필수 항목입니다.OFFSET ... FETCH절
OFFSET은 은필수 다니입항목과 함께 입니다.FETCH당신은 수없다니습할을 사용할 수 .ORDER BY ... FETCH.
TOP 결합할 수 없습니다.OFFSET그리고.FETCH동일한 쿼리 식에 있습니다.

총 결과 수를 가져오는 작업과 페이징 작업은 서로 다릅니다.이 예를 위해, 당신이 다루고 있는 쿼리가

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행이 반환됩니다.여기서 특히 웹 앱의 멋진 점은 반환할 행 번호를 제외하고 어떤 상태도 유지할 필요가 없다는 것입니다.

놀랍게도, 다른 어떤 대답도 모든 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

"구어 술어"

그@previousScore그리고.@previousPlayerId값은 이전 페이지의 마지막 레코드의 각 값입니다.이렇게 하면 "다음" 페이지를 가져올 수 있습니다.에 약에만.ORDER BY은 방은향입니다.ASC 순한사용을 사용합니다.>대신.

위의 방법을 사용하면 이전 40개의 레코드를 먼저 가져오지 않고는 즉시 4페이지로 이동할 수 없습니다.하지만 종종, 여러분은 어쨌든 그렇게 멀리 뛰고 싶지 않을 것입니다.대신 인덱싱에 따라 데이터를 일정한 시간 내에 가져올 수 있는 훨씬 빠른 쿼리를 얻을 수 있습니다.또한 기본 데이터가 변경되더라도 페이지는 "안정적"으로 유지됩니다(예: 사용자가 4페이지에 있는 동안 1페이지에서).

예를 들어 웹 응용 프로그램에서 더 많은 데이터를 로드하는 것이 번거로울 때 페이지를 구현하는 가장 좋은 방법입니다.

참고로, "검색 방법"은 키 집합 페이지화라고도 합니다.

페이지 이전의 총 레코드 수

그COUNT(*) OVER()창 기능은 "페이지화 전" 총 레코드 수를 계산하는 데 도움이 됩니다.2000을 Server 2000에 두 가지 .COUNT(*).

부터는 SQL Server 2012를 사용할 수 .OFFSET그리고.FETCH NEXT페이지화를 수행할 절입니다.

SQL Server의 경우 다음을 시도해 보십시오.

SQL Server 2012에서는 설정된 데이터의 최적화를 쿼리하기 위해 ORDER BY 절에 새로운 기능이 추가되어 T-SQL로 작성하는 모든 사용자와 SQL Server의 전체 실행 계획에 대한 데이터 페이징 작업이 더 쉬워졌습니다.

이전 예제에서 사용한 것과 동일한 논리를 사용하는 T-SQL 스크립트 아래.
--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를 사용하여 쿼리 페이징

MSDN: ROW_NUMBER(트랜잭션-SQL)

각 파티션의 첫 번째 행에 대해 1부터 시작하여 결과 집합의 파티션 내에 있는 행의 순서 번호를 반환합니다.

다음 예제에서는 주문 날짜 순서대로 숫자 50에서 60 사이의 행을 반환합니다.

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

다양한 페이징 기술에 대한 좋은 개요는 http://www.codeproject.com/KB/aspnet/PagingLarge.aspx 에서 확인할 수 있습니다.

ROWCOUNT 방법은 SQL Server 2000(2005년 및 2008년에도 사용할 예정이며 ROW_NUMBER와 비교하여 성능을 측정할 뿐)에서 주로 사용해 왔습니다. 이 방법은 매우 빠르지만 정렬된 열에 고유한 값이 있는지 확인해야 합니다.

SQL Server 2000의 경우 IDITY 열이 있는 테이블 변수를 사용하여 ROW_NUMBER()를 시뮬레이션할 수 있습니다.

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(인덱스 사용 생략)을 사용할 때의 성능 오버헤드를 발생시키지 않습니다.단점은 데이터 세트가 매우 크고 마지막 페이지에 가까운 경우 사용된 임시 공간의 양입니다.그런 경우에는 커서 성능을 테스트하지 않았지만, 더 나을 수도 있습니다.

이 접근 방식은 데이터의 첫 페이지에 최적화될 수 있습니다.또한 TOP은 SQL Server 2000에서 변수를 허용하지 않으므로 ROWCOUNT를 사용했습니다.

sql server 2012에서 페이징을 수행하는 가장 좋은 방법은 오프셋을 사용하고 저장 프로시저에서 다음을 가져오는 것입니다.OFFSET 키워드 - 절별 순서로 오프셋을 사용하면 쿼리는 OFFSET n개의 행에 지정한 레코드 수를 건너뜁니다.

FETCH NEXT 키워드 - 절별 순서만 Fetch Next를 사용하면 페이징에 표시할 행 수가 반환되고 오프셋이 없으면 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

다음은 사용하기 쉽고 빠른 것 같습니다.페이지 번호만 설정하면 됩니다.

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)

다음 방법을 사용해 보십시오.

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

이것들은 SQL 서버 측에서 쿼리 결과를 페이징하기 위한 저의 솔루션입니다.이러한 접근 방식은 SQL Server 2008과 2012 간에 다릅니다.또한 하나의 컬럼으로 필터링 및 오더 개념을 추가하였습니다.그리드 보기에서 페이징 및 필터링을 수행하고 순서를 지정할 때 매우 효율적입니다.

테스트하기 전에 샘플 테이블 하나를 만들고 이 테이블에 행을 삽입해야 합니다. (실제로는 테이블 필드를 고려하여 Where 절을 변경해야 하며 선택의 주요 부분에 조인 및 하위 쿼리가 있을 수 있습니다.)

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 서버 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

SQL Server 2008+에서 CTE를 사용하는 두 번째 솔루션

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;

부터는 2012년부터 할 수 .OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

2012년 SO 질문의 중복입니다. 효율적인 페이징 구현 방법입니다.

[TableX]에서 [FieldX] 간격띄우기 500행 다음 100행만 가져오기

여기서는 이 주제에 대해 더 자세히 설명하고 대안적인 접근 방식을 사용합니다.

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 텍스트를 생성할 수 있습니다.

   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

다른 init_id일 경우 idx를 다시 시작합니다.

의 ROW_NUMBER사용할 정렬 열이 없는 경우 다음과 같이 사용할 수 있습니다.

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

이것은 심지어 70만 개의 테이블 크기 이상의 검색에서 잘 작동했습니다.

이것은 레코드 11에서 30을 가져옵니다.

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

이 비트는 SQL Server 및 MySQL의 최신 버전을 사용하여 페이지를 이동할 수 있는 기능을 제공하며 각 행의 총 행 수를 전달합니다.기본 키를 사용하여 고유 행 수를 계산합니다.

WITH T AS
(  
  SELECT TABLE_ID, ROW_NUMBER() OVER (ORDER BY TABLE_ID) AS RN
  , (SELECT COUNT(TABLE_ID) FROM TABLE) AS TOTAL 
  FROM TABLE (NOLOCK)
)

SELECT T2.FIELD1, T2.FIELD2, T2.FIELD3, T.TOTAL 
FROM TABLE T2 (NOLOCK)
INNER JOIN T ON T2.TABLE_ID=T.TABLE_ID
WHERE T.RN >= 100
AND T.RN < 200

언어나 사용 중인 드라이버를 지정하지 않았습니다.그러므로 저는 그것을 추상적으로 묘사하고 있습니다.

스크롤 가능한 결과 집합/데이터 세트를 만듭니다.테이블에 주 서버가 있어야 합니다.
끝까지 뛰어오르다
행 수를 요청합니다.
페이지의 첫머리로 이동
페이지 끝까지 행을 스크롤합니다.

언급URL : https://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server

'code' 카테고리의 다른 글

Azure 앱 서비스를 64비트로 변경하는 방법 (0)	2023.05.03
Xcode 4 - 콘솔/로그 창 분리 (0)	2023.05.03
Mongoose 채우기 대 개체 중첩 (0)	2023.05.03
Web API 속성 라우팅의 선택적 매개변수 (0)	2023.05.03
부분 선언은 서로 다른 기본 클래스를 지정할 수 없습니다. (0)	2023.04.28

현재글SQL Server에서 결과를 페이지로 이동하는 가장 좋은 방법은 무엇입니까?

각종 프로그래밍 정보를 다루는 블로그입니다.

mysql, asp.net, ReactJS, spring-boot, Git, sql-server, ajax, angularJS, wordpress, PYTHON, Excel, Oracle, json, jQuery, bash, MariaDB, PowerShell, mongodb, SWIFT, C,

Today :
Yesterday :

starcafe

SQL Server에서 결과를 페이지로 이동하는 가장 좋은 방법은 무엇입니까?

SQL Server에서 결과를 페이지로 이동하는 가장 좋은 방법은 무엇입니까?

"구어 술어"

페이지 이전의 총 레코드 수

'code' 카테고리의 다른 글

'code'의 다른글

티스토리툴바

« 2025/02 »
일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28

SQL Server에서 결과를 페이지로 이동하는 가장 좋은 방법은 무엇입니까?

SQL Server에서 결과를 페이지로 이동하는 가장 좋은 방법은 무엇입니까?

"구어 술어"

페이지 이전의 총 레코드 수

'code' 카테고리의 다른 글

'code'의 다른글

관련글

티스토리툴바