티스토리 뷰

 

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 절이 고유한 열 또는 열의 조합을 포함해야 합니다.


'SQL' 카테고리의 다른 글

SQL Server 2012 T-SQL: CHOOSE, IIF 논리함수  (0) 2012.03.28
SQL Server 2012의 강화된 T-SQL: 시퀀스(Sequence)  (0) 2012.03.21
SQL Server 2012 RTM  (0) 2012.03.07
Hadoop  (0) 2012.02.13
Big Data  (0) 2012.01.19
댓글