열 집합 사용
적용 대상:
SQL Server 2016(13.x) 이상 Azure SQL DatabaseAzure 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
다음 단계
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기