자동 값 생성

3분

특정 테이블의 한 열에 자동으로 일련의 값을 생성해야 하는 경우도 있습니다. Transact-SQL은 테이블의 특정 열에 IDENTITY 속성을 사용하거나, SEQUENCE 개체를 정의하고 해당 개체에서 생성된 값을 사용하는 두 가지 방법을 제공합니다.

IDENTITY 속성

IDENTITY 속성을 사용하려면 소수 자릿수가 0인 숫자 데이터 형식(즉 정수만)을 사용하여 열을 정의하고 IDENTITY 키워드를 포함합니다. 허용되는 형식에는 소수 자릿수 0을 명시적으로 지정하는 모든 정수 형식 및 10진 형식이 포함됩니다.

선택적인 초기값(시작 값) 및 증분(단계 값)도 지정할 수 있습니다. 초기값과 증분을 지정하지 않으면 1로 설정됩니다.

참고

열 정의에서 NULL 또는 NOT NULL을 지정하는 대신 IDENTITY 속성이 지정됩니다. IDENTITY 속성이 있는 모든 열은 자동으로 null을 허용하지 않습니다. 자체 설명서에만 NOT NULL을 지정할 수 있지만 열을 NULL로 지정하면(즉 null을 허용하면) 테이블 생성 문에서 오류가 발생합니다.

테이블의 열 하나만 IDENTITY 속성 집합을 가질 수 있습니다. 기본 키 또는 대체 키로 자주 사용됩니다.

다음 코드에서는 이전 섹션 예제에서 사용된 Sales.Promotion 테이블의 생성을 보여 주지만 이번에는 PromotionID라는 ID 열을 기본 키를 사용합니다.

CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);

참고

CREATE TABLE 문에 대한 자세한 내용은 이 모듈에서 다루지 않습니다.

ID 열에 데이터 삽입

열에 IDENTITY 속성이 정의되면 테이블에 대한 INSERT 문은 일반적으로 IDENTITY 열의 값을 지정하지 않습니다. 데이터베이스 엔진은 열에 다음으로 사용 가능한 값을 사용하여 값을 생성합니다.

예를 들어 PromotionID 열의 값을 지정하지 않고 Sales.Promotion 테이블에 행을 삽입할 수 있습니다.

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

VALUES 절에 PromotionID 열의 값이 포함되지 않더라도 INSERT 절에 열 목록을 지정할 필요가 없습니다. ID 열은 이 요구 사항에서 제외됩니다.

이 행이 테이블에 삽입된 첫 번째 행인 경우 결과는 다음과 같은 새 행입니다.

PromotionID

PromotionName

StartDate

ProductModelID

할인

메모

클리어런스 세일

2021-01-01T00:00:00

0.1

10% 할인

테이블을 만들 때 IDENTITY 열에 대해 초기값 또는 증분이 설정되지 않았으므로 첫 번째 행이 값 1로 삽입됩니다. 삽입할 다음 행에는 PromotionID 값 2가 할당되는 식입니다.

ID 값 검색

동일한 세션 및 범위 내에서 가장 최근에 할당된 IDENTITY 값을 반환하려면 다음과 같이 SCOPE_IDENTITY 함수를 사용합니다.

SELECT SCOPE_IDENTITY();

SCOPE_IDENTITY 함수는 테이블의 현재 범위에서 생성된 가장 최근의 ID 값을 반환합니다. 특정 테이블에 최신 ID 값이 필요한 경우 다음과 같이 IDENT_CURRENT 함수를 사용할 수 있습니다.

SELECT IDENT_CURRENT('Sales.Promotion');

ID 값 재정의

자동으로 생성된 값을 재정의하고 IDENTITY 열에 특정 값을 할당하려는 경우 먼저 SET IDENTITY INSERT table_name ON 문을 사용하여 ID 삽입을 활성화해야 합니다. 이 옵션을 사용하면 다른 열과 마찬가지로 ID 열에 명시적 값을 삽입할 수 있습니다. 완료되면 SET IDENTITY INSERT table_name OFF 문을 사용하여 명시적인 초기값으로 입력한 마지막 값을 사용하여 자동 ID 값 사용을 계속할 수 있습니다.

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

알아본 대로 IDENTITY 속성은 테이블 내의 열에 대한 값의 시퀀스를 생성하는 데 사용됩니다. 그러나 IDENTITY 속성은 데이터베이스 내의 여러 테이블에서 값을 조정하는 데 적합하지 않습니다. 예를 들어 조직에서 직접 판매와 재판매인에 대한 판매를 구별하고 판매 데이터를 별도의 테이블에 저장하려는 경우를 가정해 보겠습니다. 두 종류의 판매 모두 고유한 청구서 번호를 필요로 할 수 있으며 두 가지 종류의 판매에 대해 동일한 값을 복제하는 것을 방지하고자 할 수 있습니다. 이 요구 사항에 대한 한 가지 해결책은 두 테이블에서 고유한 순차 값의 풀을 유지 관리하는 것입니다.

ID 열 다시 시드

경우에 따라 열의 ID 값을 다시 설정하거나 건너뛰어야 합니다. 이렇게 하려면 DBCC CHECKIDENT 함수를 사용하여 열을 “다시 시드”합니다. 이를 사용하여 많은 값을 건너뛰거나 테이블의 모든 행을 삭제한 후 다음 ID 값을 1로 다시 설정할 수 있습니다. DBCC CHECKIDENT 사용에 관한 자세한 내용은 Transact-SQL 참조 설명서를 참조하세요.

SEQUENCE

Transact-SQL에서 시퀀스 개체를 사용하여 특정 테이블과 독립적으로 새 순차 값을 정의할 수 있습니다. 시퀀스 개체는 CREATE SEQUENCE 문을 사용하여 만들고 선택적으로 데이터 형식,(정수 형식, 10진수 또는 소수 자릿수가 0인 숫자여야 함) 시작 값, 증분 값, 최댓값, 성능와 관련된 기타 옵션을 제공합니다.

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

시퀀스에서 다음으로 사용 가능한 값을 검색하려면 다음과 같이 NEXT VALUE FOR 구문을 사용합니다.

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

IDENTITY 또는 SEQUENCE

IDENTITY 열을 사용할지 또는 값을 자동으로 채우기 위해 SEQUENCE 개체를 사용할지 여부를 결정할 때는 다음 사항을 염두에 두어야 합니다.

애플리케이션의 요구 사항이 여러 테이블 간 또는 테이블 내의 여러 열 간 단일 계열의 숫자를 공유하는 것이라면 SEQUENCE를 사용합니다.
SEQUENCE를 사용하여 다른 열로 값을 정렬할 수 있습니다. NEXT VALUE FOR 구문은 OVER 절을 사용하여 정렬 열을 지정할 수 있습니다. OVER 절을 사용하면 반환된 값이 OVER 절의 ORDER BY 절 순서에 따라 생성됩니다. 이 기능을 사용하면 행이 SELECT에서 반환될 때 행 번호를 생성할 수도 있습니다. 다음 예제에서 Production.Product 테이블은 이름 열을 따라 정렬되고 첫 번째 반환 열은 순차적 숫자입니다.
```
SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
    ProductID,
    Name
FROM Production.Product;
```
이전 명령문이 표시할 SEQUENCE 값을 선택했더라도 값은 여전히 ‘사용된’ 상태이며 표시된 SEQUENCE 값을 더 이상 사용할 수 없습니다. 위의 SELECT를 여러 번 실행하는 경우 매번 다른 SEQUENCE 값을 얻게 됩니다.
애플리케이션에 여러 숫자를 동시에 할당해야 하는 경우 SEQUENCE를 사용합니다. 예를 들어 애플리케이션이 일련 번호를 다섯 개 예약해야 하는 경우가 여기에 해당합니다. ID 값을 요청하는 경우 다른 프로세스가 동시에 번호를 요청하면 시리즈에 간격이 발생할 수 있습니다. sp_sequence_get_range 시스템 프로시저를 사용하여 시퀀스의 여러 숫자를 한 번에 검색할 수 있습니다.
SEQUENCE를 사용하면 증분 값 등 시퀀스의 사양을 변경할 수 있습니다.
IDENTITY 값은 업데이트로부터 보호됩니다. IDENTITY 속성으로 열을 업데이트하려고 하면 오류가 발생합니다.

피드백

이 페이지가 도움이 되었나요?