IDENTITY를 사용하여 전용 SQL 풀에서 서로게이트 키 만들기

아티클
02/04/2025

이 문서에서는 속성을 사용하여 전용 SQL 풀의 IDENTITY 테이블에 서로게이트 키를 만들기 위한 권장 사항 및 예제를 찾습니다.

서로게이트 키란?

테이블의 서로게이트 키는 각 행에 대해 고유 식별자가 있는 열입니다. 테이블 데이터에서 키가 생성되지 않습니다. 데이터 웨어하우스 모델을 설계하는 경우 데이터 모델러는 해당 테이블에 서로게이트 키를 만들려고 합니다. 이 속성을 사용하여 부하 성능에 IDENTITY 영향을 주지 않고 이 목표를 간단하고 효과적으로 달성할 수 있습니다.

참고 항목

Azure Synapse Analytics에서:

IDENTITY 값은 각 배포에서 자체적으로 증가하며 다른 배포의 IDENTITY 값과 겹치지 않습니다. Synapse의 IDENTITY 값은 사용자가 IDENTITY를 사용하여 중복 값을 SET IDENTITY_INSERT ON 명시적으로 삽입하거나 다시 시딩하는 경우 고유하지 않습니다. 자세한 내용은 CREATE TABLE(Transact-SQL) IDENTITY(속성)를 참조하세요.
배포 열의 UPDATE는 IDENTITY 값이 고유하다는 것을 보장하지 않습니다. 고유성을 확인하려면 배포 열에 대한 UPDATE 후에 DBCC CHECKIDENT(Transact-SQL)를 사용합니다.

IDENTITY 열이 있는 테이블 만들기

이 IDENTITY 속성은 부하 성능에 영향을 주지 않고 전용 SQL 풀의 모든 배포에서 확장되도록 설계되었습니다. 따라서 IDENTITY 구현은 이러한 목표를 달성하는 데 지향됩니다.

다음 문과 유사한 구문을 사용하여 테이블을 처음 만들 때 테이블을 속성이 있는 IDENTITY 것으로 정의할 수 있습니다.

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 INT NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

테이블을 채우는 데 INSERT..SELECT를 사용할 수 있습니다.

이 섹션의 나머지 부분에서는 구현을 보다 완벽하게 이해하는 데 도움이 되는 뉘앙스를 강조 표시합니다.

값 할당

이 속성은 IDENTITY 데이터 웨어하우스의 분산 아키텍처로 인해 서로게이트 값이 할당되는 순서를 보장하지 않습니다. 이 IDENTITY 속성은 부하 성능에 영향을 주지 않고 전용 SQL 풀의 모든 배포에서 확장되도록 설계되었습니다.

다음 예제는 그림입니다.

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 VARCHAR(30) NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

INSERT INTO dbo.T1
VALUES (NULL);

INSERT INTO dbo.T1
VALUES (NULL);

SELECT *
FROM dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

앞의 예제에서 두 개의 행이 배포 1에서 연결되었습니다. 첫 번째 행에는 열 C1의 서로게이트 값 1이 있고 두 번째 행에는 서로게이트 값 61이 있습니다. 이 두 값은 모두 속성에 IDENTITY 의해 생성되었습니다. 그러나 값 할당은 연속되지 않습니다. 이 동작은 의도된 것입니다.

불균형 데이터

데이터 형식 값의 범위는 배포에 균등하게 분산되어 있습니다. 배포된 테이블에 불균형 데이터가 발생한 경우 데이터 형식으로 사용할 수 있는 값의 범위는 중간에 소진될 수 있습니다. 예를 들어 모든 데이터가 단일 분포에서 끝난 경우 테이블은 데이터 형식 값의 1/6에만 효율적으로 액세스할 수 있습니다. 이러한 이유로 속성은 IDENTITY 데이터 형식으로 INT 만 제한됩니다 BIGINT .

SELECT..INTO

기존 IDENTITY 열을 새 테이블로 선택하면 다음 조건 중 하나가 충족되지 않는 한 새 열이 속성을 상속합니다 IDENTITY .

문은 SELECT 조인을 포함합니다.
여러 SELECT 문이 .를 사용하여 UNION조인됩니다.
열이 IDENTITY 목록에 두 번 이상 나열됩니다 SELECT .
열은 IDENTITY 식의 일부입니다.

이러한 조건 중 하나가 true이면 속성을 상속하는 대신 열이 IDENTITY 만들어집니다NOT NULL.

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS)는 설명된 것과 동일한 SQL Server 동작을 SELECT..INTO따릅니다. 그러나 문 부분의 열 정의 CREATE TABLE 에는 속성을 지정할 IDENTITY 수 없습니다. 또한 CTAS의 일부에서 함수를 SELECT 사용할 IDENTITY 수 없습니다. 테이블을 채우려면 CREATE TABLE를 사용하여 INSERT..SELECT 다음의 테이블을 정의해야 합니다.

IDENTITY 열에 명시적 값 삽입

전용 SQL 풀은 SET IDENTITY_INSERT <your table> ON|OFF 구문을 지원합니다. 이 구문을 사용하여 열에 값을 명시적으로 삽입할 IDENTITY 수 있습니다.

많은 데이터 모델러는 해당 차원에 있는 특정 행에 미리 정의된 음수 값을 사용하려고 합니다. 예를 들어 -1 또는 알 수 없는 멤버 행이 있습니다.

다음 스크립트는 다음을 사용하여 SET IDENTITY_INSERT이 행을 명시적으로 추가하는 방법을 보여줍니다.

SET IDENTITY_INSERT dbo.T1 ON;

INSERT INTO dbo.T1
(   C1,
    C2
)
VALUES (-1,'UNKNOWN');

SET IDENTITY_INSERT dbo.T1 OFF;

SELECT     *
FROM    dbo.T1;

데이터 로드

속성의 IDENTITY 존재는 데이터 로드 코드에 몇 가지 영향을 줍니다. 이 섹션에서는 를 사용하여 IDENTITY테이블에 데이터를 로드하기 위한 몇 가지 기본 패턴을 강조 표시합니다.

테이블을 사용하여 데이터를 로드하고 서로게이트 키를 IDENTITY생성하려면 테이블을 만든 다음 로드를 사용 INSERT..SELECT 하거나 INSERT..VALUES 수행합니다.

다음 예제에서는 기본 패턴을 강조 표시합니다.

--CREATE TABLE with IDENTITY
CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1),
     C2 VARCHAR(30)
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

--Use INSERT..SELECT to populate the table from an external table
INSERT INTO dbo.T1
(C2)
SELECT     C2
FROM    ext.T1;

SELECT *
FROM   dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

참고 항목

열이 있는 테이블에 IDENTITY 데이터를 로드할 때는 현재 사용할 CREATE TABLE AS SELECT 수 없습니다.

데이터 로드에 대한 자세한 내용은 전용 SQL 풀에 대한 ELT(추출, 로드 및 변환) 설계 및 로드 모범 사례를 참조하세요.

시스템 보기

sys.identity_columns 카탈로그 뷰를 사용하여 속성이 있는 IDENTITY 열을 식별할 수 있습니다.

이 예제에서는 데이터베이스 스키마를 보다 잘 이해할 수 있도록 다른 시스템 카탈로그 뷰와 sys.identity_columns를 통합하는 방법을 보여줍니다.

SELECT  sm.name
,       tb.name
,       co.name
,       CASE WHEN ic.column_id IS NOT NULL
             THEN 1
        ELSE 0
        END AS is_identity
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
LEFT JOIN   sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

제한 사항

속성을 IDENTITY 사용할 수 없습니다.

열 데이터 형식이 아닌 INT 경우 또는 BIGINT
열이 배포 키인 경우
테이블이 외부 테이블인 경우

다음 관련 함수는 전용 SQL 풀에서 지원되지 않습니다.

일반 작업

다음 샘플 코드를 사용하여 열을 사용할 때 IDENTITY 일반적인 작업을 수행할 수 있습니다.

C1 열은 IDENTITY 다음 작업 모두에 있습니다.

테이블에 가장 높게 할당된 값을 확인합니다.

MAX() 함수를 사용하여 배포된 테이블에 할당된 가장 높은 값을 확인합니다.

SELECT MAX(C1)
FROM dbo.T1

IDENTITY 속성에 대한 초기값 및 증분을 찾습니다.

다음 쿼리를 사용하여 테이블에 대한 ID 증분 및 초기 구성 값을 검색하기 위해 카탈로그 뷰를 사용할 수 있습니다.

SELECT  sm.name
,       tb.name
,       co.name
,       ic.seed_value
,       ic.increment_value
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
JOIN        sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

다음을 통해 공유

IDENTITY를 사용하여 전용 SQL 풀에서 서로게이트 키 만들기

서로게이트 키란?

IDENTITY 열이 있는 테이블 만들기

값 할당

불균형 데이터

SELECT..INTO

CREATE TABLE AS SELECT

IDENTITY 열에 명시적 값 삽입

데이터 로드

시스템 보기

제한 사항

일반 작업

테이블에 가장 높게 할당된 값을 확인합니다.

IDENTITY 속성에 대한 초기값 및 증분을 찾습니다.

피드백

추가 리소스

다음을 통해 공유

IDENTITY를 사용하여 전용 SQL 풀에서 서로게이트 키 만들기

서로게이트 키란?

IDENTITY 열이 있는 테이블 만들기

값 할당

불균형 데이터

SELECT..INTO

CREATE TABLE AS SELECT

IDENTITY 열에 명시적 값 삽입

데이터 로드

시스템 보기

제한 사항

일반 작업

테이블에 가장 높게 할당된 값을 확인합니다.

IDENTITY 속성에 대한 초기값 및 증분을 찾습니다.

관련 콘텐츠

피드백

추가 리소스