열 집합 사용

아티클
03/22/2024

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL 데이터베이스 Azure SQL Managed Instance

스파스 열을 사용하는 테이블에서는 테이블의 모든 스파스 열을 반환하도록 열 집합을 지정할 수 있습니다. 열 집합은 테이블의 모든 스파스 열을 구조화된 출력으로 결합하는 형식화되지 않은 XML 표현입니다. 열 집합은 테이블에 물리적으로 저장되지 않는다는 점에서 계산된 열과 같습니다. 열 집합은 직접 업데이트할 수 있다는 점에서 계산 열과 다릅니다.

테이블의 열 수가 많고 개별적으로 작업하는 것이 번거로운 경우 열 집합을 사용하는 것이 좋습니다. 애플리케이션은 열이 많은 테이블에서 열 집합을 사용하여 데이터를 선택하고 삽입하면 성능이 약간 향상될 수 있습니다. 그러나 많은 인덱스가 테이블의 열에 정의되면 열 집합의 성능이 저하될 수 있습니다. 이는 실행 계획에 필요한 메모리 양이 증가하기 때문입니다.

열 집합을 정의하려면 CREATE TABLE 또는 ALTER TABLE 문에 *<column_set_name>* FOR ALL_SPARSE_COLUMNS 키워드를 사용합니다.

열 집합 사용 지침

열 집합을 사용하는 경우 고려해야 할 지침은 다음과 같습니다.

스파스 열 및 열 집합을 동일한 문의 일부로 추가할 수 있습니다.
테이블에 이미 스파스 열이 포함되어 있으면 열 집합을 테이블에 추가할 수 없습니다.
열 집합 열은 변경하거나 이름을 바꿀 수 없습니다. 열 집합을 변경하려면 스파스 열과 열 집합을 삭제하고 다시 만들어야 합니다. SPARSE 키워드가 있는 열은 테이블에서 추가하고 삭제할 수 있습니다.
스파스 열을 포함하지 않는 테이블에 열 집합을 추가할 수 있습니다. 스파스 열이 나중에 테이블에 추가되면 열 집합에 표시됩니다.
테이블당 하나의 열 집만 허용됩니다.
열 집합은 선택 사항이며 스파스 열을 사용하는 데 필수가 아닙니다.
제약 조건 또는 기본값은 열 집합에 정의할 수 없습니다.
계산 열은 열 집합 열을 포함할 수 없습니다.
열 집합이 포함된 테이블에서는 분산 쿼리가 지원되지 않습니다.
복제는 열 집합을 지원하지 않습니다.
변경 데이터 캡처는 열 집합을 지원하지 않습니다.
열 집합은 모든 종류의 인덱스의 일부가 될 수 없습니다. 여기에는 XML 인덱스, 전체 텍스트 인덱스 및 인덱싱된 뷰가 포함됩니다. 열 집합은 인덱스에 포함된 열로 추가할 수 없습니다.
열 집합은 필터링된 인덱스 또는 필터링된 통계의 필터 식에 사용할 수 없습니다.
뷰에 열 집합이 포함되면 해당 열 집합이 XML 열로 뷰에 표시됩니다.
열 집합은 인덱싱된 뷰 정의에 포함할 수 없습니다.
분할된 뷰에서 이름으로 스파스 열을 지정하는 경우 열 집합이 포함된 테이블을 포함하는 분할된 뷰는 업데이트할 수 있습니다. 분할된 뷰는 열 집합을 참조할 때 업데이트할 수 없습니다.
열 집합을 참조하는 쿼리 알림은 허용되지 않습니다.
XML 데이터의 크기 제한은 2GB입니다. 행에 있는 NULL이 아닌 모든 스파스 열의 결합된 데이터가 이 제한을 초과하면 쿼리 또는 DML 작업에서 오류가 발생합니다.
COLUMNS_UPDATED 함수에서 반환되는 데이터에 대한 자세한 내용은 스파스 열 사용을 참조하세요.

열 집합에서 데이터 선택에 대한 지침

열 집합에서 데이터를 선택하는 경우 고려해야 할 지침은 다음과 같습니다.

이론적으로 열 집합은 기본 관계형 열 집합을 단일 XML 표현으로 집계하는 업데이트 가능 XML 계산 열 유형입니다. 열 집합은 ALL_SPARSE_COLUMNS 속성만 지원합니다. 이 속성은 특정 행에 대해 모든 스파스 열의 Null이 아닌 값을 모두 집계하는 데 사용됩니다.
SQL Server Management Studio 테이블 편집기에서 열 집합은 편집할 수 있는 XML 필드로 표시됩니다. 열 집합을 다음 형식으로 정의합니다.
```
<column_name_1>value1</column_name_1><column_name_2>value2</column_name_2>...  
```
열 집합 값의 예제는 다음과 같습니다.
```
<sparseProp1>10</sparseProp1><sparseProp3>20</sparseProp3>  
```
```
<DocTitle>Bicycle Parts List</DocTitle><Region>West</Region>
```
null 값을 포함하는 스파스 열은 열 집합에 대한 XML 표현에서 생략됩니다.

경고

열 집합을 추가하면 SELECT * 쿼리의 동작이 변경됩니다. 쿼리는 열 집합을 XML 열로 반환하며 개별 스파스 열은 반환하지 않습니다. 스키마 디자이너 및 소프트웨어 개발자는 기존 애플리케이션에서 오류가 발생하지 않도록 주의해야 합니다. 개별 스파스 열은 SELECT 문에서 이름으로 계속 쿼리할 수 있습니다.

열 집합에서 데이터 삽입 또는 수정

스파스 열의 데이터 조작은 개별 열의 이름을 사용하거나 열 집합의 이름을 참조하고, 열 집합의 XML 형식을 사용하여 열 집합의 값을 지정함으로써 수행할 수 있습니다. 스파스 열은 XML 열에서 어떤 순서로든 표시할 수 있습니다.

XML 열 집합을 사용하여 스파스 열 값을 삽입하거나 업데이트하는 경우 기본 스파스 열에 삽입되는 값은 xml 데이터 형식에서 암시적으로 변환됩니다. bigint, int, smallint, tinyint, bit, float 및 real을 포함한 숫자 데이터 형식 대부분의 경우 열에 대한 XML의 빈 값이 빈 문자열로 변환됩니다. 그러면 다음 예제와 같이 0이 열에 삽입됩니다. 그러나 0으로의 대체는 숫자 및 10진수 데이터 형식에 적용되지 않습니다. 이러한 값은 지정해야 하며 그렇지 않으면 변환 오류가 발생합니다.

CREATE TABLE t (i int SPARSE, cs xml column_set FOR ALL_SPARSE_COLUMNS);  
GO  
INSERT t(cs) VALUES ('<i/>');  
GO  
SELECT i FROM t;  
GO

이 예에서는 i 열에 대한 값이 지정되지 않았지만 0 값이 삽입되었습니다.

sql_variant 데이터 형식 사용

sql_variant 날짜 형식은 int, char 및 date와 같은 여러 다른 데이터 형식을 저장할 수 있습니다. 열 집합은 sql_variant 값에 연결된 소수 자릿수, 전체 자릿수 및 로캘 정보와 같은 데이터 형식 정보를 생성된 XML 열에서 특성으로 출력합니다. 사용자 지정하여 생성된 XML 문에 있는 이러한 특성을 열 집합의 삽입 또는 업데이트 작업에 대한 입력으로 제공하려는 경우에는 이 특성 중 일부가 필요하여 이러한 일부 특성에 기본값이 할당됩니다. 다음 표에는 값이 제공되지 않으면 서버에서 생성하는 데이터 형식 및 기본값이 니와 있습니다.

데이터 형식	localeID*	sqlCompareOptions	sqlCollationVersion	SqlSortId	최대 길이	전체 자릿수	소수 자릿수
char, varchar, binary	-1	'Default'	0	0	8000	해당 없음**	해당 없음
nvarchar	-1	'Default'	0	0	4000	해당 없음	해당 없음
decimal, float, real	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음	18	0
integer, bigint, tinyint, smallint	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음
datetime2	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음	7
날짜/시간 오프셋	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음	7
datetime, date, smalldatetime	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음
money, smallmoney	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음
time	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음	해당 없음	7

* -1 localeID는 기본 로캘을 의미합니다. 영어 로캘은 1033입니다.

** 해당 사항 없음 = 열 집합에서 선택 작업을 수행하는 동안 이러 특성에 대한 값이 출력되지 않습니다. 삽입 또는 업데이트 작업에서 열 집합에 대해 제공된 XML 표현의 호출자에 의해 이 특성에 대한 값이 지정된 경우 오류를 생성합니다.

보안

열 집합의 보안 모델은 테이블과 열 사이에 있는 보안 모델과 비슷하게 작동합니다. 열 집합은 미니 테이블로 시각화할 수 있으며 선택 작업은 이 미니 테이블에 대한 SELECT * 작업과 비슷합니다. 그러나 열 집합과 스파스 열 집합 간의 관계는 엄격하게 컨테이너가 아닌 그룹화 관계입니다. 보안 모델은 열 집합 열의 보안을 확인하고 기본 스파스 열에 대한 DENY 작업을 적용합니다. 보안 모델의 추가 특성은 다음과 같습니다.

테이블의 다른 열과 마찬가지로 열 집합 열에서 보안 권한을 부여하고 철회할 수 있습니다.
열 집합 열에 대한 SELECT, INSERT, UPDATE, DELETE 및 REFERENCES 권한의 GRANT 또는 REVOKE는 해당 집합의 기본 멤버 열로 전파되지 않습니다. 열 집합 열의 사용에만 적용됩니다. 열 집합에 대한 DENY 권한은 테이블의 기본 스파스 열로 전파니다.
열 집합 열에서 SELECT, INSERT, UPDATE 및 DELETE 문을 실행하려면 사용자가 열 집합 열에 대한 해당 권한과 테이블의 모든 스파스 열에 대한 해당 권한이 있어야 합니다. 열 집합은 테이블의 모든 스파스 열을 나타내므로 모든 스파스 열에 대한 권한이 있어야 하며, 여기에는 변경하지 않을 수 있는 스파스 열도 포함됩니다.
스파스 열 또는 열 집합에 대해 REVOKE 문을 실행하면 보안이 기본적으로 해당 부모 개체의 보안으로 설정됩니다.

예제

다음 예제에서 문서 테이블에는 DocID 및 Title 열의 공통 집합이 포함되어 있습니다. Production 그룹은 모든 생산 문서에 대한 ProductionSpecification 및 ProductionLocation 열을 원하며, Marketing 그룹은 마케팅 문서에 대한 MarketingSurveyGroup 열을 원합니다.

A. 열 집합을 포함하는 테이블 만들기

다음 예제에서는 스파스 열을 사용하고 SpecialPurposeColumns 열 집합을 포함하는 테이블을 만듭니다. 이 예제에서는 두 행을 테이블에 삽입한 다음, 테이블에서 데이터를 선택합니다.

참고

이 테이블에는 테이블을 보다 잘 표시하고 읽을 수 있도록 열이 5개만 있습니다.

USE AdventureWorks2022;  
GO  
  
CREATE TABLE DocumentStoreWithColumnSet  
    (DocID int PRIMARY KEY,  
     Title varchar(200) NOT NULL,  
     ProductionSpecification varchar(20) SPARSE NULL,  
     ProductionLocation smallint SPARSE NULL,  
     MarketingSurveyGroup varchar(20) SPARSE NULL,  
     MarketingProgramID int SPARSE NULL,  
     SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);  
GO

B. 스파스 열 이름을 사용하여 테이블에 데이터 삽입

다음 예제에서는 두 행을 예제 A에서 만든 테이블에 삽입합니다. 이 예제에서는 스파스 열의 이름을 사용하고 열 집합은 참조하지 않습니다.

INSERT DocumentStoreWithColumnSet (DocID, Title, ProductionSpecification, ProductionLocation)  
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);  
GO  
  
INSERT DocumentStoreWithColumnSet (DocID, Title, MarketingSurveyGroup)  
VALUES (2, 'Survey 2142', 'Men 25 - 35');  
GO

C. 열 집합 이름을 사용하여 테이블에 데이터 삽입

다음 예제에서는 세 번째 행을 예제 A에서 만든 테이블에 삽입합니다. 이번에는 스파스 열의 이름이 사용되지 않습니다. 대신 열 집합 이름이 사용되고 삽입 작업으로 인해 XML 형식의 스파스 열 4개 중 2개에 대한 값이 제공됩니다.

INSERT DocumentStoreWithColumnSet (DocID, Title, SpecialPurposeColumns)  
VALUES (3, 'Tire Spec 2', '<ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>');  
GO

D. SELECT *가 사용되는 경우의 열 집합 결과 관찰

다음 예제에서는 열 집합이 포함된 테이블에서 모든 열을 선택합니다. 스파스 열의 결합된 값이 포함된 XML 열을 반환합니다. 스파스 열을 개별적으로 반환하지 않습니다.

SELECT DocID, Title, SpecialPurposeColumns FROM DocumentStoreWithColumnSet ;

결과 집합은 다음과 같습니다.

DocID  Title        SpecialPurposeColumns  
1      Tire Spec 1  <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>  
2      Survey 2142  <MarketingSurveyGroup>Men 25 - 35</MarketingSurveyGroup>  
3      Tire Spec 2  <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>

E. 이름별로 열 집합을 선택한 결과 관찰

Production 부서가 마케팅 데이터에 관심이 없으므로 다음 예제에서는 출력을 제한하기 위해 WHERE 절을 추가합니다. 이 예제에서는 열 집합의 이름을 사용합니다.

SELECT DocID, Title, SpecialPurposeColumns  
FROM DocumentStoreWithColumnSet  
WHERE ProductionSpecification IS NOT NULL ;

결과 집합은 다음과 같습니다.

DocID  Title        SpecialPurposeColumns  
1      Tire Spec 1  <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>  
3      Tire Spec 2  <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>

F. 이름별로 스파스 열을 선택한 결과 관찰

테이블에 열 집합이 포함되어 있으면 다음 예에서처럼 개별 열 이름을 사용하여 테이블을 쿼리할 수 있습니다.

SELECT DocID, Title, ProductionSpecification, ProductionLocation   
FROM DocumentStoreWithColumnSet  
WHERE ProductionSpecification IS NOT NULL ;

결과 집합은 다음과 같습니다.

DocID  Title        ProductionSpecification ProductionLocation`  
1      Tire Spec 1  AXZZ217                 27`  
3      Tire Spec 2  AXW9R411                38`

G. 열 집합을 사용하여 테이블 업데이트

다음 예제에서는 세 번째 레코드를 해당 행에서 사용되는 두 스파스 열 모에 대한 새 값으로 업데이트합니다.

UPDATE DocumentStoreWithColumnSet  
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification><ProductionLocation>38</ProductionLocation>'  
WHERE DocID = 3 ;  
GO

중요

열 집합을 사용하는 UPDATE 문은 테이블의 모든 스파스 열을 업데이트합니다. 참조되지 않는 스파스 열은 NULL로 업데이트됩니다.

다음 예제에서는 세 번째 레코드를 업데이트하지만 채워진 두 열 중 하나의 값만 지정합니다. 두 번째 ProductionLocation 열은 UPDATE 문에 포함되지 않고 NULL로 업데이트됩니다.

UPDATE DocumentStoreWithColumnSet  
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification>'  
WHERE DocID = 3 ;  
GO

다음 단계

스파스 열 사용

다음을 통해 공유

열 집합 사용

열 집합 사용 지침

열 집합에서 데이터 선택에 대한 지침

열 집합에서 데이터 삽입 또는 수정

sql_variant 데이터 형식 사용

보안

예제

A. 열 집합을 포함하는 테이블 만들기

B. 스파스 열 이름을 사용하여 테이블에 데이터 삽입

C. 열 집합 이름을 사용하여 테이블에 데이터 삽입

D. SELECT *가 사용되는 경우의 열 집합 결과 관찰

E. 이름별로 열 집합을 선택한 결과 관찰

F. 이름별로 스파스 열을 선택한 결과 관찰

G. 열 집합을 사용하여 테이블 업데이트

다음 단계

피드백

추가 리소스