IT/Develop

MS-SQL에서 시퀀스(SEQUENCE)를 사용해 보자!!

Dsuny 2017. 7. 4. 13:59

 안녕하세요? (거의 3개월 만에 포스팅을 하게 되었습니다. ㅠㅠ) 이번에는 MS-SQL에서 시퀀스(SEQUENCE)를 생성하고 사용하는 방법을 알아 보겠습니다. MS-SQL의 시퀀스(SEQUENCE)SQL Server 2012 버전부터 적용이 되었습니다.

 

 SEQUENCE는 그 뜻(1.서열, 2.순서, 3.배열) 처럼 순서를 나타내는 것으로 1, 2, 3, 4, 5, 6 ... 를 사용하고 관리 할 수 있습니다. 오라클(Oracle)의 NEXTVAL과 똑같은 동작을 하는 것으로 이해 하시면 됩니다.

 

 

 

 우선 비슷한 기능인 "ID 사양"에 대해서 알아 보겠습니다. MS-SQL에서 테이블을 만들때 각 컬럼의 속성중에 "ID 사양"이라는 속성을 볼 수 있습니다.  해당 속성을 적용하면 "ID 증가값", "ID 초기값"을 설정할 수 있습니다. 테이블에 행이 추가 될때(또는 시도를 할때) 마다 "ID 초기값"에 설정된 값을 시작으로 "ID 증가값" 만큼 변하게 됩니다.

 

 

 

 위 그림과 같이 TEST_TABLE 이름으로 테이블을 생성했습니다. 그리고 아래 그림과 같이 쿼리를 수행하고 결과를 확인 할 수 있습니다. "ID 사양"으로 설정된 ID_COL의 값이 행이 추가 될떄 마다 1식 증가하는 것을 알 수 있습니다.

 

 

 

 그런데 시퀀스(SEQUENCE)는 비슷하지만 다릅니다. "ID 사양"은 테이블에 항상 종속적입니다. 예를 들어 A, B 이름의 테이블이 있고 각 테이블에 A.SEQ(DECIMAL), B.SEQ(DECIMAL) 처럼 SEQ 컬럼이 있습니다. SEQ 컬럼은 A, B 테이블에 행이 추가 될때(또는 시도를 할때) 마다 값이 증가 해야 합니다.

 

 문법은 맞지 않지만 아래 그림과 같은 결과를 얻고 싶습니다. 테이블에 종속적이지 않고 순차적으로 값을 관리하고 싶을때 시퀀스(SEQUENCE)를 사용하시면 됩니다.

 

 

 

 

1. SSMS에서 시퀀스(SEQUENCE) 생성

 

시퀀스(SEQUENCE)를 생성해 보겠습니다. 먼저 SSMS(Microsoft SQL Server Management Studio)를 이용하여 생성해 보겠습니다.

 

SSMS개체 탐색기에서 아래 그림과 같이 프로그래밍 기능 밑에 시퀀스를 찾을 수 있습니다(만약 시퀀스를 찾을 수 없다는 SQL 버전이 2012 이상인지 확인해 보시기 바랍니다). 시퀀스에서 마우스 오른쪽 버튼을 클릭하여 새 시퀀스(N)를 실행 합니다.

 

 

 

 

 시퀀스 설정을 아래 그림과 같이 입력 하였습니다. 최댓값을 5로 하고 순환하도록 하였습니다. 확인 버튼을 클릭합니다.

 

이름(N) : SEQ_TEST

데이터 형식(D) : decimal

시작 값(T) : 1

증가값(I) : 2

최솟값(M) : 1

최댓값(X) : 5

순환(C) : 체크

캐시 없음(O) : 선택

 

 

 

 

 아래 그림과 값이 시퀀스(SEQUENCE)를 수행(NEXT VALUE FOR SEQ_TEST)하면 1부터 시작해서 2씩 증가하여 최댓값 5를 넘어가면 최소값부터 다시 시작하는 것을 확인 할 수 있습니다. 여기서 주의해야 할 점은 시작값 부터 다시 순환하는게 아니라 최소값부터 순환 합니다.

 

 

 

 

2. SSMS에서 시퀀스(SEQUENCE) 수정

 

 이번에는 시퀀스(SEQUENCE)를 수정해 보겠습니다. SSMS의 개체 탐색기에서 생성된 스퀀스 SEQ_TEST에서 오른쪽 마우스를 클릭하여 속성(R)을 실행 합니다.

 

 

 

 아래 그림을 살펴 보면 새 시퀀스 화면가 다른 점을 찾을 수 있습니다. 다시 시작 순서(R)의 옵션이 보입니다. 다시 시작 순서(R)을 체크하고 값을 입력 후 확인 버튼을 클릭하면 시퀀스의 현재 값이 입력한 값으로 변경 됩니다.

 

 


 

3. SSMS에서 시퀀스(SEQUENCE) 삭제

 

 삭제 방법은 개체 탐색기에서 삭제할 스퀀스에서 마우스 오른쪽 버튼을 클릭하여 삭제(D)를 실행 하면 됩니다.

 

 

4. 쿼리로 시퀀스(SEQUENCE) 생성

 

 이번에는 쿼리로 시퀀스(SEQUENCE)를 생성해 보겠습니다. 이전에 UI에서 작업했던 사항을 쿼리로 변경한 것 밖에 없습니다. 일반적인 쿼리 명령어와 비슷 합니다. CREATE SEQUENCE 시퀀스명 AS 타입 입니다. 그 다음으로 추가적인 옵션 사항입니다. 옵션사항이지만 될 수 있으면 지정해서 사용하시는게 좋을 것 같습니다.

 

START WITH 100 : 시퀀스가 시작하는 값(100)

INCREMENT BY 2 : 증가값(2) (음수로 지정하여 감소하게 할 수도 있음)

MINVALUE 100 : 최솟값

MAXVALUE 110 : 최댓값

CYCLE : 순환

CACHE 1 : 캐쉬 (1로 지정하는 경우 NO CACHE)

 

 

 CYCLE은 CYCLE 또는 NO CYCLE 값을 사용할 수 있습니다. NO CYCLE 인 경우 스퀀스 값이 MAXVALUE 값을 넘어가면 예외가 발생합니다.

 CACHE는 시스템 번호를 생성하는 데 필요한 디스크 IO 수를 최소화하여 시퀀스 개체를 사용하는 응용 프로그램의 성능을 향상시킵니다. 기본값으로 CACHE가 됩니다. CACHE 옵션을 사용하여 만들 경우 전원 오류와 같은 예기치 않은 종료로 인해 캐시에 남아 있는 시퀀스 번호가 손실될 수 있습니다. 값을 1로 설정하는 경우 캐쉬가 없이 스퀀스가 실행되므로 디스크 IO가 매번 일어 납니다.

 

구문

CREATE SEQUENCE [schema_name . ] sequence_name
 [ AS [ built_in_integer_type | user-defined_integer_type ] ]
 [ START WITH <constant> ]
 [ INCREMENT BY <constant> ]
 [ { MINVALUE [ <constant> ] } | { NOMINVALUE } ]
 [ { MAXVALUE [ <constant> ] } | { NOMAXVALUE } ]
  [ CYCLE | { NOCYCLE } ]
 [ { CACHE [ <constant> ] } | { NO CACHE } ]
 [ ; ]

 

MSDN CREATE SEQUENCE(Transact-SQL)

 

 

5. 쿼리로 시퀀스(SEQUENCE) 수정

 

 시퀀스(SEQUENCE)를 수정해 보겠습니다. 쿼리 구문은 일반적인 쿼리 명령어와 비슷합니다. ALTER SEQUENCE 시퀀스명 입니다. START WITH 대신에 RESTART WITH 옵션(시퀀스의 현재 값이 입력한 값으로 변경)이 있습니다. 각 옵션은 위에서 설명한 내용과 동일 합니다.

 

 

구문

ALTER SEQUENCE [schema_name. ] sequence_name
 [ RESTART [ WITH <constant> ] ]
 [ INCREMENT BY <constant> ]
 [ { MINVALUE <constant> } | { NO MINVALUE } ]
 [ { MAXVALUE <constant> } | { NO MAXVALUE } ]
 [ CYCLE | { NO CYCLE } ]
 [ { CACHE [ <constant> ] } | { NO CACHE } ]
 [ ; ]

 

MSDN ALTER SEQUENCE(Transact-SQL)

 

 

6. 쿼리로 시퀀스(SEQUENCE) 삭제

 

 시퀀스(SEQUENCE)를 삭제해 보겠습니다. 쿼리 구문은 일반적인 쿼리 명령어와 비슷합니다. DROP SEQUENCE 스퀀스명 입니다. 언제나 삭제 작업을 할때는 2번~3번 확인 후 삭제를 하시기 바랍니다.

 

 

구문

DROP SEQUENCE { [ database_name . [ schema_name ] . | schema_name. ]    sequence_name } [ ,...n ]
 [ ; ]

 

MSDN DROP SEQUENCE(Transact-SQL)

 

 

 지금 까지 MS-SQL시퀀스(SEQUENCE) 사용방법을 알아 보았습니다. 그동안 시퀀스(SEQUENCE)처럼 동작하는 기능을 만들기 위해서 많은 노력을 하였었습니다. 기능을 좀더 일찍 사용할 수 있도록 하였다면 MS-SQL 사용자가 좀더 많아졌을 것 같은 생각이듭니다. 작은 차이지가 명품을 만듭니다.