MS-SQL Server

SQL Server 2012 (Denali) 신규 기능 Sequence 소개

임현수 2010. 12. 17. 21:03
오라클에만 존재하던 시퀀스 오브젝트가 드디어 SQL Server 2012 버전에 추가되었습니다.
오라클로 DB를 입문했던 저로써는 매우 반가운 소식이였습니다. 그래서 가볍게 테스트 해 본 결과 공유드립니다.

1. 기본 문법

CREATE SEQUENCE [schema_name . ] sequence_name

        [ <sequence_property_assignment> [ ,…®|n ] ]

    [ ; ]

 

<sequence_property_assignment>::=

{

    [ AS { built_in_integer_type | user-defined_integer_type } ]

    | START WITH <constant>

        | INCREMENT BY <constant>

        | { MINVALUE <constant> | NO MINVALUE }

        | { MAXVALUE <constant> | NO MAXVALUE }

        | { CYCLE | NO CYCLE }

        | { CACHE [<constant> ] | NO CACHE }

}

 

NEXT VALUE FOR [ database_name . ] [ schema_name . ]  sequence_name

   [ OVER (<over_order_by_clause>) ]

 

2. 시퀀스 객체를 활용한 채번 방식/옵션 변경에 따른 채번 성능 테스트 (참고. 개인 PC에서 테스트 진행함)
2-1. NOCACHE 에서 채번 테스트

CREATE SEQUENCE SeqNo_nocache

START WITH 1

INCREMENT BY 1

no Cache;

GO

 

declare

@seqval int = 0

,       @Cnt    int = 0;

 

select getdate();

 

while (@Cnt <= 1000000)

begin

SELECT @seqval = NEXT VALUE FOR SeqNo_nocache;

set @Cnt = @Cnt + 1;

end

 

select GETDATE();

 

Result> 21

2010-12-17 20:28:32.870

2010-12-17 20:28:53.127

2-2. CACHE 10/50/100 상태에서 채번 테스트

CREATE SEQUENCE SeqNo_Cache10

START WITH 1

INCREMENT BY 1

Cache 10;

GO

 

SELECT * FROM sys.sequences WHERE name = 'SeqNo_Cache10'

 

declare

           @seqval int = 0

,          @Cnt    int = 0;

 

select getdate();

 

while (@Cnt <= 1000000)

begin

           SELECT @seqval = NEXT VALUE FOR SeqNo_Cache100;

           set @Cnt = @Cnt + 1;

end

 

select GETDATE();

 

Result> Cache 10 : 약9초

2010-12-17 20:31:40.393

2010-12-17 20:31:49.953

 

Result> Cache 50 : 약9초

2010-12-17 20:32:41.960

2010-12-17 20:32:50.643

 

Result> cache 100 : 약9초

2010-12-17 20:34:24.297

2010-12-17 20:34:33.090

2-3. sp_sequence_get_range 사용하여 채번 (하나씩)

declare

           @seqval int = 0

,          @Cnt    int = 0

,          @range_first_value sql_variant

,          @range_first_value_output sql_variant;

 

select getdate();

 

while (@Cnt <= 1000000)

begin

           EXEC sp_sequence_get_range

                @sequence_name = N'seqno_cache10'

           ,    @range_size = 1

           ,    @range_first_value = @range_first_value_output OUTPUT ;

 

           set @Cnt = @Cnt + 1;

end

 

select GETDATE();

 

 

Result> 54

2010-12-17 20:38:00.330

2010-12-17 20:38:54.593

2-4. sp_sequence_get_range 사용하여 채번 (필요한 범위를 한방에~ )

declare

           @seqval int = 0

,          @Cnt    int = 0

,          @range_first_value sql_variant

,          @range_first_value_output sql_variant

,          @range_last_value_output sql_variant;

 

select getdate();

 

EXEC sp_sequence_get_range

           @sequence_name = N'seqno_cache10'

,          @range_size = 1000000

,          @range_first_value = @range_first_value_output OUTPUT

,          @range_last_value = @range_last_value_output output;

 

select @range_first_value_output, @range_last_value_output

 

select GETDATE();

 

Result> 0

2010-12-17 20:41:02.427

2010-12-17 20:41:02.427

3. 하나의 쿼리에서 동일한 시퀀스를 여러번 호출하면 어떻게 될까?
select next value for SeqNo_Cache10, next value for SeqNo_Cache10

 

Result> 동일한 값이 나온다.
5000151 5000151


4. 시퀀스에 over 구문은 왜 있을까?
4-1. over 절 사용 안하는 경우

select
        x.name
,       next value for SeqNo_Cache10  as seqNo
from (select top 100 name from sys.columns) x

Result
> 이름이 정렬되어지지 않은 상태로 채번되어진다.



4-2. over절 사용하는 경우 (update, merge 구문과 같이 사용 불가함)

select 
        x.name
,       next value for SeqNo_Cache10 over (order by name) as seqNo

from (select top 100 name from sys.columns) x

Result> 이름 정렬순으로 낮은 순서로 채번된다.



5. 시퀀스 번호 재시작하기

ALTER SEQUENCE dbo.Seq1 RESTART WITH 1;

6. 테스트하고 느낀 점
 > 캐싱 개수에 상관없이 절대량 처리 속도는 거의 비슷하다. 동시성만 고려하여 캐싱 수치를 적절히 잡으면 될 것 같다.
 > 캐싱을 사용할 경우 DB서버를 재시작하면 캐싱된 번호가 사라져버릴꺼라고 생각했는데, 유지되어진다. (좋다~ ^^)
 > 대량 범위를 확보하고 싶은 경우 프로시져를 반드시 쓰기를 추천합니다.
 > Identity와 Sequence를 동시에 사용할 수 있다니, 기대만빵입니다. 어느 것을 사용할지는 사용자의 선택~

7. Itzik Ben-Gan Blog에 Sequence 에 대한 퀴즈도 있으니 관심 있으신 분은 참고하세요~

*. 참고 자료
http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx
http://www.sqlmag.com/blogs/puzzled-by-t-sql/tabid/1023/entryid/75978/TSQL-Challenge-with-Denali-s-Sequence-Object.aspx
http://sqlserverpedia.com/blog/sql-server-bloggers/2-of-my-favorite-new-t-sql-features-in-sql-server-denali-sql-11/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+sqlserverpedia+%28SQLServerPedia%29&utm_content=Google+Reader