[데이터 베이스] MSSQL SEQUENCE (시퀀스) 개체 생성 및 사용

시퀀스(Sequene) 는 단어 의미 그대로, 순서를 부여하는 것을 의미한다. 

독립 개체로 생성되는데, 순번의 중앙 저장소로 디폴트 타입은 bigint이다. 

독립 개체이므로 관리가 필요하며, IDENTITY와 동일하게 활용할 시에는 테이블 단위 개체가 필요하다.

 

여러 테이블에 공유되는 고유 순번을  생성할 수 있다.

지정 번호 도달하면 다시 시작이 필요한 경우 처음부터 다시 시작하는 cycling 을 지원하고, 순번의 조정 및 변경이 자유로운 편이다.

시퀀스 값을 다른 열을 기준으로 정렬해야 하는 경우에 NEXT VALUE OVER () 절을 사용하면 된다.

 

SEQUENCE 개체 VS. IDENTITY

 

sequence 와 identity의 차이는 다음과 같이 정리할 수 있다.

 

기능	IDENTITY	SEQUENCE
사전에 값 구하기	X	O
테이블 종속 여부	O	X
UPDATE 문 사용	X	O
SELECT 문 사용	X (SELECT INTO 가능)	O (OVER () 가능)
순서 조정	X (INSERT SELECT 가능)	X
롤백 시 재사용 여부	X	O
기존 열과 연결/해지	X	O
최소/최댓값 조정	X	O
Cycle	X	O
증가 값 변경	X	O
Cache 정의	X	O
범위 값 구하기	X	O

 

 

SEQUENCE 생성 

시퀀스 생성의 기본 형태는 다음과 같다. 

 

CREATE SEQUENCE [schema_name . ] sequence_name  
    [ 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 } ]  
    [ ; ]

 

CREATE SEQUENCE (Transact-SQL) - SQL Server | Microsoft Learn

CREATE SEQUENCE (Transact-SQL) - SQL Server

 

추후에 cycle 발생을 고려한다면 MINVALUE 값을 지정해 주는 것을 권장 한다. 

USE Northwind;
GO

CREATE SEQUENCE dbo.SeqNo
AS bigint
	START WITH 1
	INCREMENT BY 1
	 -- MINVALUE 1
	NO MAXVALUE
	CACHE 5000;
    

-- 샘플 테이블을 생성해보자 
IF OBJECT_ID('dbo.Sales', 'U') IS NOT NULL
	DROP TABLE dbo.Sales;	
GO

CREATE TABLE dbo.Sales
(
   SeqNo bigint NOT NULL	DEFAULT (NEXT VALUE FOR dbo.SeqNo)
								PRIMARY KEY NONCLUSTERED
,	SaleDate	datetime	NOT NULL
);
GO

INSERT dbo.Sales (SaleDate) VALUES (GETDATE());
SELECT * FROM dbo.Sales;

INSERT 를 반복하여 4까지 생성하였다.

 

시퀀스의 메타 데이터 확인할 때는 sys.sequences 를 사용하면 된다. 

SELECT 
	name, type_desc, modify_date
,	current_value, start_value, increment, minimum_value
,	is_cycling, is_cached, cache_size, is_exhausted
,	*
FROM sys.sequences
WHERE name = 'SeqNo;

 

 

정의한 시퀀스로 순번을 할당해 보자

-- INSERT 시

INSERT dbo.Sales (SeqNo, SaleDate)
VALUES 
	(NEXT VALUE FOR dbo.SeqNo, GETDATE())
,	(NEXT VALUE FOR dbo.SeqNo, GETDATE())
,	(NEXT VALUE FOR dbo.SeqNo, GETDATE())

SELECT * FROM dbo.Sales;

 

4+ 3 = > 총 7개의 순번이 생성된다. 

 

할당 후 할당된 순번을 확인해 보자

-- OUTPUT 활용
INSERT dbo.Sales (SeqNo, SaleDate)
OUTPUT inserted.SeqNo
VALUES (NEXT VALUE FOR dbo.SeqNo, GETDATE());


-- sys.sequences 활용
SELECT current_value	FROM sys.sequences 
WHERE object_id = OBJECT_ID('dbo.SeqNo', 'SO');

 

 

NEXT VALUE FOR로 1개의 행을 더 생성하고, OUTPUT 으로 할당된 값을 확인할 수 있다. 

Current_value는 8이 되어 있다. 

 

현재 할당된 순번부터 100개의 보조 테이블을 생성해 보자

WITH N1(Seq) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1)
   , N2(Seq) AS (SELECT 1 FROM N1 CROSS JOIN N1 AS n)
   , N3(Seq) AS (SELECT 1 FROM N2 CROSS JOIN N2 AS n)
   , Numbers(Seq) AS (SELECT ROW_NUMBER() OVER(ORDER BY n.Seq) FROM N3 CROSS JOIN N3 AS n)
SELECT SeqNo = NEXT VALUE FOR dbo.SeqNo 
FROM Numbers
WHERE Seq BETWEEN 1 AND 100;

해당 쿼리를 수행하면 9 ~  108 의 SeqNo가 생성된다. 

 

다시 current_value를 확인하면 108 임을 확인할 수 있다. 

 

OVER() 을 활용하여 정렬 순번을 생성해 보자.

SELECT 
	SeqNo = NEXT VALUE FOR dbo.SeqNo OVER (ORDER BY OrderDate DESC)
,	OrderDate
FROM dbo.Orders
WHERE OrderID BETWEEN 10248 AND (10248 + 9);

 

총 10개의 순번을 생성했으므로 current_Value는 118 이다. 

 

시퀀스의 Cycle 설정

다음으로 cycle 을 설정해 보자. 

메타를 확인하면, is_cycling= 0 임을 확인할 수 있고, 이는 현재 cycle 이 설정되지 않았음을 의미한다. 

위에서 min_value와 max_value를 설정하지 않았으니 bigint 의 최소 최대 값을 가진다. 

 

 

SeqNo의 최대 값을 150으로 바꿔주고  SELECT NEXT VALUE FOR 문으로 150을 초과할 때 까지 실행해보자 

ALTER SEQUENCE dbo.SeqNo
	MAXVALUE	150
    

-- MAX 값 초과할 때 까지 수행
SELECT NEXT VALUE FOR dbo.SeqNo;

 

150을 넘어가면 다음과 같은 에러가 발생한다.

 

 

다음과 같이 cycle을 설정하여 에러 발생을 방지하자. 

ALTER SEQUENCE dbo.SeqNo
	CYCLE;
    

-- 초기값 1로 재설정
ALTER SEQUENCE dbo.SeqNo
	RESTART WITH 1;


-- 번호 생성
SELECT SeqNo = NEXT VALUE FOR dbo.SeqNo;



-- 생성한 테이블과 시퀀스 삭제
DROP TABLE Sales;
DROP SEQUENCE dbo.SeqNo;

 

 

 

[참고] 

 SQL Server 컨설턴트가 알려주는, 쿼리 능력 레벨업(고급 T-SQL 쿼리) 강의