[MSSQL] paging 페이징 처리 OFFSET ROWS FETCH

OFFSET 및 FETCH를 사용하여 쿼리에서 반환되는 행 수를 제한합니다.

 

예제

   A. DepartmentID 열로 정렬된 모든 행을 반환합니다.  

SELECT DepartmentID, Name, GroupName  
FROM HumanResources.Department  
ORDER BY DepartmentID;

정렬된 모든 행을 반환

  

   B. 정렬된 결과 집합에서 처음 5개 행을 건너뛰고 나머지 행을 모두 반환합니다.

SELECT DepartmentID, Name, GroupName  
FROM HumanResources.Department  
ORDER BY DepartmentID 
OFFSET 5 ROWS;

처음 5개 행을 건너뛰고 나머지 행을 모두 반환

 

   C. 0개 행을 건너뛰고 정렬된 결과 집합에서 처음 10개 행만 반환합니다.

SELECT DepartmentID, Name, GroupName  
FROM HumanResources.Department  
ORDER BY DepartmentID   
OFFSET 0 ROWS  
FETCH NEXT 10 ROWS ONLY;

처음 10개 행만 반환

 

   D. 하위 쿼리는 dbo.AppSettings 테이블의 PageSize 열에서 단일 값을 반환

CREATE TABLE dbo.AppSettings (AppSettingID INT NOT NULL, PageSize INT NOT NULL);  
GO  
INSERT INTO dbo.AppSettings VALUES(1, 10);  
GO  
DECLARE @StartingRowNumber TINYINT = 5;  

SELECT DepartmentID, Name, GroupName  
FROM HumanResources.Department  
ORDER BY DepartmentID ASC   
OFFSET @StartingRowNumber ROWS   
FETCH NEXT (SELECT PageSize FROM dbo.AppSettings WHERE AppSettingID = 1) ROWS ONLY;

 

 

참조

• ORDER BY Clause (Transact-SQL) - SQL Server | Microsoft Learn