블로그 이미지
정홍주
Azure에 대한 내용뿐만 아니라 새로운 트렌드로 빅데이터, BI, SharePoint, 앱 등의 내용을 다룹니다.

calendar

        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 29 30 31

Notice

2012.03.21 08:30 SQL

SQL Server 2012의 강화된 T-SQL: 시퀀스(Sequence)

SQL Server 2012의 시퀀스에 대한 내용을 알아보도록 하겠습니다. 이전버전에서는 IDENTITY 라는 속성을 통해 순차적인 일련번호를 생성했습니다. 새롭게 시퀀스라는 개체가 제공되어 일련번호를 효과적으로 사용할 수 있을 것 같은데 아래에서 한번 살펴보도록 하겠습니다.

 

먼저 아래와 같이 데이터베이스에 테이블을 생성합니다.

 

CREATE TABLE Products

(

    ProductID INT NOT NULL PRIMARY KEY

    ,Name VARCHAR(200) NULL

,ModifiedDate DATETIME DEFAULT GETDATE()

);

 

Products 테이블에 ProductID를 일련번호로 생성하기 위해서는 IDENTITY를 사용했지만 별도의 시퀀스(Sequence) 개체를 아래와 같이 생성할 수 있습니다.

CREATE SEQUENCE PIdSequence AS INT

    START WITH 1

    INCREMENT BY 1;

 

초기값 1 , 증가값 1로 시퀀스(Sequence) 개체를 생성했습니다. 생성된 개체는 개체탐색기의 프로그래밍 기능, 시퀀스에서 살펴볼 수 있습니다.


시퀀스(Sequence)의 개체를 더블 클릭하면 아래와 같은 대화 상자를 볼 수 있는데 현재 값이나 다시 시작 순서, CACHE 에 대한 내용을 알 수 있습니다.

 

 위에서 생성한 Products 테이블에 시퀀스(Sequence) 개체를 적용해보도록 하겠습니다.

INSERT 구문 등에서 NEXT VALUE FOR PIdSequence 를 사용하면 됩니다.

 

INSERT INTO Products (ProductID, Name)

   VALUES (NEXT VALUE FOR PIdSequence, 'Coffece');

 

위 구문을 여러 번 실행하여 Products를 여러 개를 입력한 결과는 아래와 같습니다.

 

/*

1           Coffece             2012-03-19 09:54:00.327

2           Coffece             2012-03-19 09:54:00.913

3           Coffece             2012-03-19 09:54:01.400

4           Coffece             2012-03-19 09:54:01.853

5           Coffece             2012-03-19 09:54:02.277

*/

 

l  위에서 생성한 시퀀스(Sequence) 개체는 Products 테이블에만 종속된 것은 아니며 다른 테이블에서도 사용이 가능합니다. 다른 테이블에서 사용하면 해당 값은 증가되며 증가된 값을 계속 사용하게 됩니다.

NEXT VALUE FOR PIdSequence 을 실행할 경우 시퀀스(Sequence) 값은 증가하게 됩니다.

l  중간에 값이 비어 있는 경우 SET IDENTITY_INSERT 옵션을 쓰지 않고 누락된 값을 INSERT 하면 됩니다.

l  현재 값을 알려면 아래 구문을 실행할 수 있습니다.

SELECT cache_size, current_value

FROM sys.sequences

WHERE name = 'PIdSequence' ;

 

l  복제, SSIS 등의 경우 시퀀스(Sequence)를 사용할 경우 더 효과적으로 사용이 가능합니다.

l  CACHE 옵션이 있어 대용량 데이터를 처리하는 일련번호일 경우 Sequence가 우수할 것으로 보입니다.

 

posted by 정홍주
2012.03.14 08:30 SQL

 

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의 강화된 T-SQL : 페이징 - OffSet/Fetch  (0) 2012.03.14
SQL Server 2012 RTM  (0) 2012.03.07
Hadoop  (0) 2012.02.13
Big Data  (0) 2012.01.19
posted by 정홍주
2011.11.19 12:53 SQL

새로 RC 버전이 출시되었습니다
아래 링크를 참조 하시기 바랍니다


http://www.microsoft.com/sqlserver/en/us/learning-center/resources.aspx

'SQL' 카테고리의 다른 글

Hadoop  (0) 2012.02.13
Big Data  (0) 2012.01.19
SQL Server 2012 RC  (0) 2011.11.19
PASS Summit 2011 – Big Data  (0) 2011.10.15
PASS Summit 2011 – Keynote(2)  (0) 2011.10.14
PASS Summit 2011  (0) 2011.10.13
posted by 정홍주
prev 1 2 next