CREATE TABLE(Transact-SQL) IDENTITY (Property)
적용 대상:
Microsoft Fabric의 SQL ServerAzure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL 데이터베이스
테이블에 ID 열을 만듭니다. 이 속성은 CREATE TABLE 및 ALTER TABLE Transact-SQL 문에 사용됩니다.
참고 항목
IDENTITY 속성은 열의 행 ID 속성을 노출하는 SQL-DMO Identity 속성과 다릅니다.
구문
IDENTITY [ (seed , increment) ]
이 구문은 Azure Synapse Analytics의 서버리스 SQL 풀에서 지원되지 않습니다.
인수
seed
테이블에 로드된 첫 번째 행에 사용되는 값입니다.
increment
로드된 이전 행의 ID 값에 추가되는 증분 값입니다.
참고 항목
Azure Synapse Analytics에서 ID 값은 데이터 웨어하우스의 분산 아키텍처로 인해 증분되지 않습니다. 자세한 내용은 IDENTITY를 사용하여 Synapse SQL 풀에서 서로게이트 키를 만드는 방법을 참조 하세요.
초기값과 증가값을 모두 지정하거나 모두 지정하지 않아야 합니다. 둘 다 지정하지 않은 경우에는 기본값 (1,1)이 사용됩니다.
설명
ID 열은 키 값을 생성하는 데 사용할 수 있습니다. 열의 ID 속성은 다음 조건을 보장합니다.
각 새 값은 현재 시드 및 증가에 따라 생성됩니다.
특정 트랜잭션에 대한 각각의 새 값은 테이블의 각 동시 트랜잭션마다 다릅니다.
열의 ID 속성은 다음 조건을 보장하지 않습니다.
값 의 고유성 - 고유성은 또는
PRIMARY KEY제약 조건 또는UNIQUEUNIQUE인덱스로 적용해야 합니다.참고 항목
Azure Synapse Analytics는 지원 또는 제약 조건 또는
UNIQUEUNIQUE인덱스를 지원하지PRIMARY KEY않습니다. 자세한 내용은 IDENTITY를 사용하여 Synapse SQL 풀에서 서로게이트 키를 만드는 방법을 참조 하세요.트랜잭션 내의 연속 값 - 여러 행을 삽입하는 트랜잭션은 테이블에서 다른 동시 삽입이 발생할 수 있으므로 행에 대한 연속 값을 가져올 수 없습니다. 값이 연속되어야 하는 경우 트랜잭션은 테이블에서 배타적 잠금을 사용하거나 격리 수준을 사용해야
SERIALIZABLE합니다.서버 다시 시작 또는 다른 실패 후 연속적인 값 - SQL Server는 성능상의 이유로 ID 값을 캐싱할 수 있으며 데이터베이스 오류 또는 서버 재시작 중에 지정된 값의 일부가 손실될 수 있습니다. 그러면 삽입 시 ID 값에서 간격이 발생할 수 있습니다. 간격이 허용되지 않는 경우 애플리케이션은 자체 메커니즘을 사용하여 키 값을 생성해야 합니다. 옵션과 함께
NOCACHE시퀀스 생성기를 사용하면 커밋되지 않은 트랜잭션으로의 간격을 제한할 수 있습니다.값 재사용 - 특정 초기값/증가가 있는 지정된 ID 속성의 경우 ID 값은 엔진에서 다시 사용되지 않습니다. 특정 insert 문이 실패하거나 insert 문이 롤백되면 사용된 ID 값이 손실되고 다시 생성되지 않습니다. 그 결과 후속 ID 값이 생성될 때 간격이 발생할 수 있습니다.
이러한 제한은 성능을 향상시키기 위해 디자인의 일부이며, 많은 일반적인 상황에서 허용되기 때문입니다. 이러한 제한 사항으로 인해 ID 값을 사용할 수 없는 경우 현재 값을 포함하는 별도의 테이블을 만들고 애플리케이션을 사용하여 테이블 및 번호 할당에 대한 액세스를 관리합니다.
ID 열이 있는 테이블이 복제를 위해 게시된 경우 해당 ID 열은 사용되는 복제 유형에 적합한 방식으로 관리해야 합니다. 자세한 내용은 ID 열 복제를 참조하세요.
ID 열은 테이블당 하나만 만들 수 있습니다.
메모리 최적화 테이블에서 시드 및 증분은 .로 1, 1설정해야 합니다. 초기값 또는 증분을 다음 오류가 The use of seed and increment values other than 1 is not supported with memory optimized tables발생하는 값이 아닌 1 값으로 설정합니다.
열에 ID 속성이 설정되면 제거할 수 없습니다. 새 데이터 형식이 ID 속성과 호환되는 한 데이터 형식을 변경할 수 있습니다.
예제
A. CREATE TABLE에서 IDENTITY 속성 사용
다음 예에서는 자동으로 ID를 증가시키는 IDENTITY 속성을 사용하여 새 테이블을 만듭니다.
USE AdventureWorks2022;
GO
IF OBJECT_ID('dbo.new_employees', 'U') IS NOT NULL
DROP TABLE new_employees;
GO
CREATE TABLE new_employees (
id_num INT IDENTITY(1, 1),
fname VARCHAR(20),
minit CHAR(1),
lname VARCHAR(30)
);
INSERT new_employees (fname, minit, lname)
VALUES ('Karin', 'F', 'Josephs');
INSERT new_employees (fname, minit, lname)
VALUES ('Pirkko', 'O', 'Koskitalo');
B. ID 값의 간격을 찾기 위해 제네릭 구문을 사용합니다.
다음 예에서는 데이터가 제거되었을 때 ID 값에서 간격을 찾기 위한 일반 구문을 보여 줍니다.
참고
다음 Transact-SQL 스크립트의 첫 번째 부분은 설명 목적으로만 제공되는 것입니다. -- Create the img table 주석으로 시작하는 Transact- SQL 스크립트를 실행할 수 있습니다.
-- Here is the generic syntax for finding identity value gaps in data.
-- The illustrative example starts here.
SET IDENTITY_INSERT tablename ON;
DECLARE @minidentval column_type;
DECLARE @maxidentval column_type;
DECLARE @nextidentval column_type;
SELECT @minidentval = MIN($IDENTITY),
@maxidentval = MAX($IDENTITY)
FROM tablename
IF @minidentval = IDENT_SEED('tablename')
SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
FROM tablename t1
WHERE $IDENTITY BETWEEN IDENT_SEED('tablename')
AND @maxidentval
AND NOT EXISTS (
SELECT *
FROM tablename t2
WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('tablename')
)
ELSE
SELECT @nextidentval = IDENT_SEED('tablename');
SET IDENTITY_INSERT tablename OFF;
-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column
-- called id_num, which is an increasing identification number, and the
-- second column called company_name.
-- This is the end of the illustration example.
-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF OBJECT_ID('dbo.img', 'U') IS NOT NULL
DROP TABLE img;
GO
CREATE TABLE img (
id_num INT IDENTITY(1, 1),
company_name SYSNAME
);
INSERT img (company_name)
VALUES ('New Moon Books');
INSERT img (company_name)
VALUES ('Lucerne Publishing');
-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON;
DECLARE @minidentval SMALLINT;
DECLARE @nextidentval SMALLINT;
SELECT @minidentval = MIN($IDENTITY)
FROM img
IF @minidentval = IDENT_SEED('img')
SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
FROM img t1
WHERE $IDENTITY BETWEEN IDENT_SEED('img')
AND 32766
AND NOT EXISTS (
SELECT *
FROM img t2
WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img')
)
ELSE
SELECT @nextidentval = IDENT_SEED('img');
SET IDENTITY_INSERT img OFF;