SQL Server 2012의 강화된 T-SQL : 페이징 - OffSet/Fetch

 

SQL Server 2012의 강화된 T-SQL : 페이징 - OffSet/Fetch

 

개발자들이 리스트나 게시판에 대해서 페이징 하는 것은 기본적인 작업입니다.

물론 여러 가지 방법이 있습니다. TOP 구문과 WHERE 절을 통해서 하거나 ROW_NUMBER를 통해서 처리하거나 별도 쿼리를 통해서 하거나 기존 소스를 한번 살펴보면 여러 방법을 쓰고 있다는 것을 알 수 있습니다. 

SQL Server 2012에서는 Offset/Fetch 구문을 통해 효과적으로 페이징 쿼리를 작성할 수 있습니다. 일단 쿼리 구문이 간단해서 작업하기가 용이한 것 같습니다.

물론 저희 환경의 기존 데이터에 기존 페이징 쿼리와 Offset/Fetch 쿼리에 대한 성능 비교를 해봐야 하겠지만 여기에서는 간단한 사용법을 알아보도록 하겠습니다. 

자 먼저 구문을 한번 실행해보도록 하겠습니다.

아래 구문은 AdventureWorks2012에 있는 Product 테이블을 SELECT 한 결과입니다.

--[AdventureWorks2012]

SELECT ProductID, Name, ProductNumber, Color, Size FROM [Production].[Product]

ORDER BY ProductID DESC  

 

 

간단한 페이징을 해보도록 하겠습니다. 위 결과의 1 페이지에 해당하는 결과만 보여주는 내용입니다. 직접 값을 지정했습니다.

SELECT ProductID, Name, ProductNumber, Color, Size FROM [Production].[Product]

ORDER BY ProductID DESC  

OFFSET 0 ROWS

    FETCH NEXT 10 ROWS ONLY;

 

2 페이지를 보고 싶다면 아래와 같이 구문이 변경됩니다.

SELECT ProductID, Name, ProductNumber, Color, Size FROM [Production].[Product]

ORDER BY ProductID DESC  

OFFSET 10 ROWS

    FETCH NEXT 10 ROWS ONLY; 

 

변수로 대체되는지를 확인하기 위해 저장 프로시저를 아래와 같이 생성했습니다.

CREATE PROC usp_GetProducts

@Page INT

AS

DECLARE @Fetch INT =10;

DECLARE @OffSet INT= (@Page-1)*@Fetch

 

SELECT ProductID, Name, ProductNumber, Color, Size FROM [Production].[Product]

ORDER BY ProductID DESC  

OFFSET @OffSet ROWS

    FETCH NEXT @Fetch ROWS ONLY;

GO

 

프로시저를 실행하면 아래와 같은 결과를 얻을 수 있습니다. 

서브 쿼리를 이용해서 할 수 도 있습니다. 아래 구문을 참고하세요~

SELECT ProductID, Name, ProductNumber, Color, Size FROM [Production].[Product]

ORDER BY ProductID DESC  

OFFSET 10 ROWS

    FETCH NEXT (SELECT PageSize FROM [dbo].[AppSettings])  ROWS ONLY;

GO

 

 기존에 쓰던 쿼리가 없어 실행계획을 비교하기는 뭐하네요~ 

온라인 설명서에서 Offset/Fetch에 대한 내용은 아래 링크를 참조하십시오.

http://msdn.microsoft.com/ko-kr/library/ms188385(v=sql.110).aspx#Offset

  

Offset Fetch에 대한 제한 사항

l  외부 쿼리와 상관 관계를 만들 수 없습니다.

l  OVER 절은 OFFSET 및 FETCH를 지원하지 않습니다.

l  OFFSET 및 FETCH는 INSERT, UPDATE, MERGE 및 DELETE 문에서 직접 지정할 수 없지만 이러한 문에 정의된 하위 쿼리에서는 지정할 수 있습니다

l  UNION, EXCEPT 또는 INTERSECT 연산자를 사용하는 쿼리에서는 쿼리 결과의 순서를 지정하는 마지막 쿼리에서만 OFFSET 및 FETCH를 지정할 수 있습니다.

l  같은 쿼리 식(같은 쿼리 범위)에서 TOP을 OFFSET 및 FETCH와 결합할 수 없습니다.

 

안정된 최적화를 위해서는 아래 조건을 만족해야 합니다.

l  쿼리에 사용되는 기본 데이터가 변경되지 않아야 합니다.즉, 쿼리와 연결된 행이 업데이트되지 않거나 페이지에 대한 모든 쿼리 요청이 스냅숏 또는 직렬화 가능 트랜잭션 격리를 사용하여 단일 트랜잭션에서 실행되어야 합니다.
(위 온라인 설명서의 “단일 트랜잭션에서 여러 쿼리 실행” 쿼리 예를 참고하십시오.)

l  ORDER BY 절이 고유한 열 또는 열의 조합을 포함해야 합니다.