스파스 열 사용Use Sparse Columns

이 항목은 다음에 적용됩니다. 예SQL Server(2016부터)예Azure SQL Database예Azure SQL Data Warehouse 예병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

스파스 열은 Null 값에 대해 최적화된 저장소가 있는 일반 열입니다.Sparse columns are ordinary columns that have an optimized storage for null values. 스파스 열을 사용하면 Null 값에 대한 공간 요구 사항이 줄어드는 반면 Null이 아닌 값을 검색하는 데 더 많은 오버헤드가 발생합니다.Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. 최소 20%에서 40% 사이의 공간이 절약되는 경우에는 스파스 열을 사용하십시오.Consider using sparse columns when the space saved is at least 20 percent to 40 percent. 스파스 열 및 열 집합은 CREATE TABLE 또는 ALTER TABLE 문을 사용하여 정의합니다.Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

스파스 열은 열 집합 및 필터링된 인덱스와 함께 사용할 수 있습니다.Sparse columns can be used with column sets and filtered indexes:

  • 열 집합Column sets

    INSERT, UPDATE 및 DELETE 문은 이름별로 스파스 열을 참조할 수 있습니다.INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. 그러나 단일 XML 열에 결합된 테이블의 모든 스파스 열을 보고 사용할 수도 있습니다.However, you can also view and work with all the sparse columns of a table that are combined into a single XML column. 이러한 열을 열 집합이라고 합니다.This column is called a column set. 열 집합에 대한 자세한 내용은 열 집합 사용을 참조하세요.For more information about column sets, see Use Column Sets.

  • 필터링된 인덱스Filtered indexes

    스파스 열은 Null 값 행을 많이 포함하므로 필터링된 인덱스에 특히 적합합니다.Because sparse columns have many null-valued rows, they are especially appropriate for filtered indexes. 스파스 열에 대한 필터링된 인덱스는 채워진 값을 포함하는 행만 인덱싱할 수 있습니다.A filtered index on a sparse column can index only the rows that have populated values. 따라서 보다 작고 효율적인 인덱스가 만들어집니다.This creates a smaller and more efficient index. 자세한 내용은 Create Filtered Indexes을(를) 참조하세요.For more information, see Create Filtered Indexes.

    스파스 열 및 필터링된 인덱스를 통해 Windows SharePoint ServicesWindows SharePoint Services와 같은 응용 프로그램에서는 SQL Server 2017SQL Server 2017을 사용하여 많은 수의 사용자 정의 속성을 효율적으로 저장하고 액세스할 수 있습니다.Sparse columns and filtered indexes enable applications, such as Windows SharePoint ServicesWindows SharePoint Services, to efficiently store and access a large number of user-defined properties by using SQL Server 2017SQL Server 2017.

스파스 열 속성Properties of Sparse Columns

스파스 열에는 다음과 같은 특징이 있습니다.Sparse columns have the following characteristics:

  • SQL Server 데이터베이스 엔진SQL Server Database Engine 은 열 정의에 SPARSE 키워드를 사용하여 해당 열의 값 저장을 최적화할 수 있습니다.The SQL Server 데이터베이스 엔진SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. 따라서 테이블에 있는 행에 대해 열 값이 NULL인 경우 값을 저장할 필요가 없습니다.Therefore, when the column value is NULL for any row in the table, the values require no storage.

  • 스파스 열을 포함하는 테이블에 대한 카탈로그 뷰는 일반적인 테이블에 대한 카탈로그 뷰와 동일합니다.Catalog views for a table that has sparse columns are the same as for a typical table. sys.columns 카탈로그 뷰는 테이블의 각 열에 대한 행을 포함하고 열 집합(정의된 경우)을 포함합니다.The sys.columns catalog view contains a row for each column in the table and includes a column set if one is defined.

  • 스파스 열은 논리적 테이블이 아닌 저장소 계층의 속성입니다.Sparse columns are a property of the storage layer, rather than the logical table. 따라서 SELECT…INTO 문은 스파스 열 속성을 새 테이블에 복사하지 않습니다.Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table.

  • COLUMNS_UPDATED 함수는 varbinary 값을 반환하여 DML 동작을 수행하는 동안 업데이트된 모든 열을 나타냅니다.The COLUMNS_UPDATED function returns a varbinary value to indicate all the columns that were updated during a DML action. COLUMNS_UPDATED 함수에 의해 반환된 비트는 다음과 같습니다.The bits that are returned by the COLUMNS_UPDATED function are as follows:

    • 스파스 열이 명시적으로 업데이트되면 해당 스파스 열에 대한 비트가 1로 설정되고 열 집합에 대한 비트가 1로 설정됩니다.When a sparse column is explicitly updated, the corresponding bit for that sparse column is set to 1, and the bit for the column set is set to 1.

    • 열 집합이 명시적으로 업데이트되면 열 집합에 대한 비트가 1로 설정되고 해당 테이블의 모든 스파스 열에 대한 비트가 1로 설정됩니다.When a column set is explicitly updated, the bit for the column set is set to 1, and the bits for all the sparse columns in that table are set to 1.

    • 삽입 작업의 경우 모든 비트가 1로 설정됩니다.For insert operations, all bits are set to 1.

      열 집합에 대한 자세한 내용은 열 집합 사용을 참조하세요.For more information about columns sets, see Use Column Sets.

    다음 데이터 형식은 SPARSE로 지정할 수 없습니다.The following data types cannot be specified as SPARSE:

geographygeography texttext
geometrygeometry timestamptimestamp
imageimage 사용자 정의 데이터 형식user-defined data types
ntextntext

데이터 형식별 예상 공간 절약Estimated Space Savings by Data Type

스파스 열을 사용하면 SPARSE로 표시되지 않은 동일한 데이터에 대해 필요한 공간보다 Null이 아닌 값에 더 많은 저장 공간이 필요합니다.Sparse columns require more storage space for nonnull values than the space required for identical data that is not marked SPARSE. 다음 표에서는 각 데이터 형식에 대한 공간 사용률을 보여 줍니다.The following tables show the space usage for each data type. NULL 백분율 열은 40%의 순 공간 절약을 위해 NULL이어야 하는 데이터 비율을 나타냅니다.The NULL Percentage column indicates what percent of the data must be NULL for a net space savings of 40 percent.

고정 길이 데이터 형식Fixed-Length Data Types

데이터 형식Data type 비-스파스 바이트Nonsparse bytes 스파스 바이트Sparse bytes NULL 백분율NULL percentage
bitbit 0.1250.125 55 98%98%
tinyinttinyint 11 55 86%86%
smallintsmallint 22 66 76%76%
intint 44 88 64%64%
bigintbigint 88 1212 52%52%
realreal 44 88 64%64%
floatfloat 88 1212 52%52%
smallmoneysmallmoney 44 88 64%64%
moneymoney 88 1212 52%52%
smalldatetimesmalldatetime 44 88 64%64%
datetimedatetime 88 1212 52%52%
uniqueidentifieruniqueidentifier 1616 2020 43%43%
datedate 33 77 69%69%

전체 자릿수 종속 길이 데이터 형식Precision-Dependent–Length Data Types

데이터 형식Data type 비-스파스 바이트Nonsparse bytes 스파스 바이트Sparse bytes NULL 백분율NULL percentage
datetime2(0)datetime2(0) 66 1010 57%57%
datetime2(7)datetime2(7) 88 1212 52%52%
time(0)time(0) 33 77 69%69%
time(7)time(7) 55 99 60%60%
datetimetoffset(0)datetimetoffset(0) 88 1212 52%52%
datetimetoffset(7)datetimetoffset (7) 1010 1414 49%49%
decimal/numeric(1,s)decimal/numeric(1,s) 55 99 60%60%
decimal/numeric(38,s)decimal/numeric(38,s) 1717 2121 42%42%
vardecimal(p,s)vardecimal(p,s) decimal 형식을 일반적인 예상치로 사용Use the decimal type as a conservative estimate.

데이터 종속 길이 데이터 형식Data-Dependent–Length Data Types

데이터 형식Data type 비-스파스 바이트Nonsparse bytes 스파스 바이트Sparse bytes NULL 백분율NULL percentage
sql_variantsql_variant 기본 데이터 형식에 따라 다름Varies with the underlying data type
varchar 또는 charvarchar or char 22 44 60%60%
nvarchar 또는 charnvarchar or nchar 22 4+4+ 60%60%
varbinary 또는 binaryvarbinary or binary 22 44 60%60%
xmlxml 22 44 60%60%
hierarchyidhierarchyid 22 44 60%60%

길이는 형식에 포함된 데이터의 평균에 2바이트 또는 4바이트를 더한 값과 같습니다.The length is equal to the average of the data that is contained in the type, plus 2 or 4 bytes.

스파스 열을 업데이트하려면 메모리 내 오버헤드가 필요함In-Memory Overhead Required for Updates to Sparse Columns

스파스 열을 사용하여 테이블을 디자인하는 경우 행이 업데이트될 때 테이블에서 null이 아닌 스파스 열 각각에 대해 2바이트의 추가 오버헤드가 필요함에 유의해야 합니다.When designing tables with sparse columns, keep in mind that an additional 2 bytes of overhead are required for each non-null sparse column in the table when a row is being updated. 이러한 추가 메모리 요구 사항으로 인해 이 메모리 오버헤드를 포함한 총 행 크기가 8019를 초과하여 열을 행에 밀어넣을 수 없으므로 예기치 않게 576 오류가 발생하여 업데이트가 실패할 수 있습니다.As a result of this additional memory requirement, updates can fail unexpectedly with error 576 when the total row size, including this memory overhead, exceeds 8019, and no columns can be pushed off the row.

bigint 형식의 600개 스파스 열을 가진 테이블의 예를 살펴 보십시오.Consider the example of a table that has 600 sparse columns of type bigint. 571개의 null이 아닌 열이 있는 경우 디스크의 총 크기는 571 * 12 = 6852바이트입니다.If there are 571 non-null columns, then the total size on disk is 571 * 12 = 6852 bytes. 추가 행 오버로드와 스파스 열 머리글을 포함하면 약 6895바이트로 증가합니다.After including additional row overhead and the sparse column header, this increases to around 6895 bytes. 페이지에는 여전히 디스크에서 사용 가능한 1124바이트가 있습니다.The page still has around 1124 bytes available on disk. 그러면 추가 열을 업데이트할 수 있는 것처럼 보일 수 있습니다.This can give the impression that additional columns can be updated successfully. 하지만 업데이트 중에 메모리에 2*(null이 아닌 스파스 열의 수)에 해당하는 추가 오버헤드가 발생합니다.However, during the update, there is additional overhead in memory which is 2*(number of non-null sparse columns). 이 예에서 추가 오버헤드(2 * 571 = 1142바이트)를 포함하면 디스크의 행 크기가 약 8037바이트로 증가합니다.In this example, including the additional overhead – 2 * 571 = 1142 bytes – increases the row size on disk to around 8037 bytes. 이는 최대 허용 크기인 8019바이트를 초과합니다.This size exceeds the maximum allowed size of 8019 bytes. 모든 열은 고정 길이 데이터 형식이므로 행에 밀어넣을 수 없습니다.Since all the columns are fixed-length data types, they cannot be pushed off the row. 따라서 576 오류가 발생하여 업데이트가 실패합니다.As a result, the update fails with the 576 error.

스파스 열 사용에 대한 제한 사항Restrictions for Using Sparse Columns

스파스 열은 모든 SQL ServerSQL Server 데이터 형식을 사용할 수 있으며 다른 모든 열처럼 작동하지만 다음과 같은 제한 사항의 적용을 받습니다.Sparse columns can be of any SQL ServerSQL Server data type and behave like any other column with the following restrictions:

  • 스파스 열은 Null을 허용해야 하며 ROWGUIDCOL 또는 IDENTITY 속성을 사용할 수 없습니다.A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties. 스파스 열의 데이터 형식은 text, ntext, image, timestamp, 사용자 정의 데이터 형식, geometry또는 geography일 수 없으며, 스파스 열에 FILESTREAM 특성을 가질 수 없습니다.A sparse column cannot be of the following data types: text, ntext, image, timestamp, user-defined data type, geometry, or geography; or have the FILESTREAM attribute.

  • 스파스 열은 기본값을 사용할 수 없습니다.A sparse column cannot have a default value.

  • 스파스 열은 규칙에 바인딩할 수 없습니다.A sparse column cannot be bound to a rule.

  • 계산 열은 스파스 열을 포함할 수 있지만 이런 경우에도 계산 열을 SPARSE로 표시할 수 없습니다.Although a computed column can contain a sparse column, a computed column cannot be marked as SPARSE.

  • 스파스 열에 대해 데이터 마스크를 정의할 수 있지만 열 집합의 일부인 스파스 열에는 정의할 수 없습니다.A data mask can be defined on a sparse column, but not on a sparse column that is part of a column set.

  • 스파스 열은 클러스터형 인덱스 또는 고유 기본 키 인덱스의 일부가 될 수 없습니다.A sparse column cannot be part of a clustered index or a unique primary key index. 그러나 스파스 열에 정의된 지속형 계산 열과 비지속형 계산 열 모두 클러스터형 키의 일부가 될 수 있습니다.However, both persisted and nonpersisted computed columns that are defined on sparse columns can be part of a clustered key.

  • 스파스 열은 클러스터형 인덱스 또는 힙의 파티션 키로 사용될 수 없습니다.A sparse column cannot be used as a partition key of a clustered index or heap. 그러나 스파스 열은 비클러스터형 인덱스의 파티션 키로 사용될 수 있습니다.However, a sparse column can be used as the partition key of a nonclustered index.

  • 스파스 열은 테이블 변수 및 테이블 반환 매개 변수에 사용되는 사용자 정의 테이블 형식의 일부가 될 수 없습니다.A sparse column cannot be part of a user-defined table type, which are used in table variables and table-valued parameters.

  • 스파스 열은 데이터 압축과 호환되지 않습니다.Sparse columns are incompatible with data compression. 따라서 스파스 열을 압축된 테이블에 추가할 수 없을 뿐만 아니라 스파스 열을 포함하는 테이블을 압축할 수도 없습니다.Therefore sparse columns cannot be added to compressed tables, nor can any tables containing sparse columns be compressed.

  • 스파스 열에서 스파스가 아닌 열로 또는 스파스가 아닌 열에서 스파스 열로 열을 변경하려면 열의 저장소 형식을 변경해야 합니다.Changing a column from sparse to nonsparse or nonsparse to sparse requires changing the storage format of the column. SQL Server 데이터베이스 엔진에서는 다음 절차를 통해 이러한 변경을 수행합니다.The SQL Server Database Engine uses the following procedure to accomplish this change:

    1. 새 저장소 크기 및 형식으로 테이블에 새 열을 추가합니다.Adds a new column to the table in the new storage size and format.

    2. 테이블의 각 행에 대해 기존 열에 저장된 값을 업데이트하고 새 열로 복사합니다.For each row in the table, updates and copies the value stored in the old column to the new column.

    3. 기존 열을 테이블 스키마에서 제거합니다.Removes the old column from the table schema.

    4. 테이블을 다시 작성하거나(클러스터형 인덱스가 없는 경우) 클러스터형 인덱스를 다시 작성하여 이전 열에 사용된 공간을 회수합니다.Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column.

    참고

    행에 허용된 최대 행 크기를 초과하는 데이터가 있으면 2단계가 실패합니다.Step 2 can fail when the size of the data in the row exceeds the maximum allowable row size. 이 데이터 크기에는 기존 열에 저장된 데이터 및 업데이트되어 새 열에 저장된 데이터의 크기가 포함됩니다.This size includes the size of the data stored in the old column and the updated data stored in the new column. 스파스 열이 포함되지 않은 테이블의 경우 8060바이트, 스파스 열이 포함된 테이블의 경우 8018바이트로 제한됩니다.This limit is 8060 bytes for tables that do not contain any sparse columns or 8018 bytes for tables that contain sparse columns. 데이터 크기로 인한 이 오류는 적합한 열을 행 외부로 밀어낸 경우에도 발생합니다.This error can occur even if all eligible columns have been pushed off-row.

  • 스파스가 아닌 열을 스파스 열로 변경하면 스파스 열이 Null이 아닌 값에 대해 더 많은 공간을 사용합니다.When you change a non-sparse column to a sparse column, the sparse column will consume more space for non-null values. 행이 최대 행 크기 제한에 가까우면 작업이 실패할 수 있습니다.When a row is close to the maximum row size limit, the operation can fail.

스파스 열을 지원하는 SQL Server 기술SQL Server Technologies That Support Sparse Columns

이 섹션에서는 다음 SQL ServerSQL Server 기술에서 스파스 열을 지원하는 방법에 대해 설명합니다.This section describes how sparse columns are supported in the following SQL ServerSQL Server technologies:

  • 트랜잭션 복제Transactional replication

    트랜잭션 복제는 스파스 열을 지원하지만 스파스 열에서 사용할 수 있는 열 집합은 지원하지 않습니다.Transactional replication supports sparse columns, but it does not support column sets, which can be used with sparse columns. 열 집합에 대한 자세한 내용은 열 집합 사용을 참조하세요.For more information about column sets, see Use Column Sets.

    SPARSE 특성 복제는 sp_addarticle 을 사용하거나 에서 아티클 속성 SQL Server Management StudioSQL Server Management Studio대화 상자를 사용하여 지정되는 스키마 옵션에 의해 결정됩니다.The replication of the SPARSE attribute is determined by a schema option that is specified by using sp_addarticle or by using the Article Properties dialog box in SQL Server Management StudioSQL Server Management Studio. 이전 버전의 SQL ServerSQL Server 에서는 스파스 열이 지원되지 않습니다.Earlier versions of SQL ServerSQL Server do not support sparse columns. 데이터를 이전 버전으로 복제해야 하는 경우 SPARSE 특성을 복제하지 않도록 지정합니다.If you must replicate data to an earlier version, specify that the SPARSE attribute should not be replicated.

    게시된 테이블의 경우 새 스파스 열을 테이블에 추가하거나 기존 열의 스파스 속성을 변경할 수 없습니다.For tables that are published, you cannot add any new sparse columns to a table or change the sparse property of an existing column. 이러한 작업이 필요한 경우 게시를 삭제한 다음 다시 만듭니다.If such an operation is required, drop and re-create the publication.

  • 병합 복제Merge replication

    병합 복제는 스파스 열 또는 열 집합을 지원하지 않습니다.Merge replication does not support sparse columns or column sets.

  • 변경 내용 추적Change tracking

    변경 내용 추적은 스파스 열 및 열 집합을 지원합니다.Change tracking supports sparse columns and column sets. 테이블에서 열 집합이 업데이트되면 변경 내용 추적이 이를 전체 행에 대한 업데이트로 처리합니다.When a column set is updated in a table, change tracking treats this as an update to the whole row. 열 집합 업데이트 작업을 통해 업데이트되는 정확한 스파스 열 집합을 알 수 있는 자세한 변경 내용 추적은 제공되지 않습니다.No detailed change tracking is provided to obtain the exact set of sparse columns that are updated through the column set update operation. 스파스 열이 DML 문을 통해 명시적으로 업데이트되는 경우에는 해당 열에 대한 변경 내용 추적이 일반적으로 작동하여 변경된 정확한 열 집합을 식별할 수 있습니다.If the sparse columns are updated explicitly through a DML statement, change tracking on them will work ordinarily and can identify the exact set of changed columns.

  • 변경 데이터 캡처Change data capture

    변경 데이터 캡처는 스파스 열을 지원하지만 열 집합은 지원하지 않습니다.Change data capture supports sparse columns, but it does not support column sets.

  • 열의 스파스 속성은 테이블을 복사할 때 보존되지 않습니다.The sparse property of a column is not preserved when the table is copied.

Examples

이 예의 Document 테이블에는 DocIDTitle열을 사용하는 공통 집합이 포함되어 있습니다.In this example, a document table contains a common set that has the columns DocID and Title. Production 그룹은 모든 생산 문서에 대한 ProductionSpecificationProductionLocation 열을 원하며,The Production group wants a ProductionSpecification and ProductionLocation column for all production documents. Marketing 그룹은 마케팅 문서에 대한 MarketingSurveyGroup 열을 원합니다.The Marketing group wants a MarketingSurveyGroup column for marketing documents. 이 예의 코드에서는 스파스 열을 사용하는 테이블을 만들고, 테이블에 두 개의 행을 삽입한 다음 테이블에서 데이터를 선택합니다.The code in this example creates a table that uses sparse columns, inserts two rows into the table, and then selects data from the table.

참고

이 테이블에는 테이블을 보다 잘 표시하고 읽을 수 있도록 열이 5개만 있습니다.This table has only five columns to make it easier to display and read. ANSI_NULL_DFLT_ON 옵션이 설정된 경우 스파스 열을 Null 허용으로 선언하는 작업은 선택 사항입니다.Declaring the sparse columns to be nullable is optional if the ANSI_NULL_DFLT_ON option is set.

USE AdventureWorks2012;  
GO  

CREATE TABLE DocumentStore  
    (DocID int PRIMARY KEY,  
     Title varchar(200) NOT NULL,  
     ProductionSpecification varchar(20) SPARSE NULL,  
     ProductionLocation smallint SPARSE NULL,  
     MarketingSurveyGroup varchar(20) SPARSE NULL ) ;  
GO  

INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)  
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);  
GO  

INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)  
VALUES (2, 'Survey 2142', 'Men 25 - 35');  
GO  

테이블에서 모든 열을 선택하려면 일반 쿼리 집합을 반환합니다.To select all the columns from the table returns an ordinary result set.

SELECT * FROM DocumentStore ;  

결과 집합은 다음과 같습니다.Here is the result set.

DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup

1 Tire Spec 1 AXZZ217 27 NULL

2 Survey 2142 NULL NULL Men 25-35

Production 부서는 마케팅 데이터에 관심이 없으므로 다음 쿼리에서처럼 관심 있는 열만 반환하는 열 목록을 사용하려고 합니다.Because the Production department is not interested in the marketing data, they want to use a column list that returns only columns of interest, as shown in the following query.

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

결과 집합은 다음과 같습니다.Here is the result set.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

참고 항목See Also

열 집합 사용 Use Column Sets
CREATE TABLE(Transact-SQL) CREATE TABLE (Transact-SQL)
ALTER TABLE(Transact-SQL) ALTER TABLE (Transact-SQL)
sys.columns(Transact-SQL)sys.columns (Transact-SQL)