[SQL Server] 스파스 열 (Sparse Column)

 

스파스 열(Sparse Column)은 SQL Server 2008의 새로운 기능으로 'NULL 값에 대해 최적화된

저장소가 있는 일반 열'로 정의할 수 있다.

 

NULL 값이 많이 들어갈 것으로 예상되는 열을 스파스 열로 지정해 놓을 경우 많은 공간 절약 효과를

볼 수 있다. 하지만 NULL 값이 별로 없는 열이라면 오히려 그 크기가 더 필요해진다.

 

즉, 스파스 열로 지정된 열에 NULL 값을 입력하면 지정된 데이터 크기보다 적게 공간을 차지하지만,

반대로 실제 값이 입력되면 오히려 공간을 더 차지하게 된다.

 

SQL Server 2008에서 한 테이블 당 최대 1,024개의 컬럼을 생성할 수 있다.

하지만 스파스 열은 여기에 포함되지 않고, 스파스 열이 생성할 수 있는 최대 개수는 100,000개이다.

그러므로 각 테이블들은 1,024개의 일반 열과 100,000개의 스파스 열을 가질 수 있다.

 

스파스 열을 지정하는 방법은 열을 정의할 때 'SPARSE' 예약어만 써주면 된다.

 

 

동일한 열을 지닌 간단한 두 테이블을 생성하고, 한 테이블에는 열 뒤에 SPARSE 예약어를 붙인다.

두 테이블에 4만 건의 데이터를 입력한다. 단, DATA 열에는 75%를 NULL 값으로 입력한다.

이렇게 NULL 값이 많이 입력된 경우의 두 테이블의 디스크 공간을 확인해보면 SPARSE 예약어를

붙인 테이블이 훨씬 더 작은 크기를 가지고 있음을 확인할 수 있다.

 

CREATE TABLE dbo.NonSparseTbl (     ID INT IDENTITY(1,1)   , DATA CHAR(100) NULL  )  GO    CREATE TABLE dbo.SparseTbl (     ID INT IDENTITY(1,1)   , DATA CHAR(100) SPARSE NULL  )  GO    DECLARE @I INT  SET @I = 0  WHILE @I < 10000    BEGIN      INSERT INTO dbo.NonSparseTbl VALUES (NULL)      INSERT INTO dbo.NonSparseTbl VALUES (NULL)      INSERT INTO dbo.NonSparseTbl VALUES (NULL)      INSERT INTO dbo.NonSparseTbl VALUES (REPLICATE('A', 100))      INSERT INTO dbo.SparseTbl VALUES (NULL)      INSERT INTO dbo.SparseTbl VALUES (NULL)      INSERT INTO dbo.SparseTbl VALUES (NULL)      INSERT INTO dbo.SparseTbl VALUES (REPLICATE('A', 100))      SET @I = @I + 1  END  GO    EXEC SP_SPACEUSED 'NonSparseTbl'  EXEC SP_SPACEUSED 'SparseTbl'  GO

 

 

이번에는 NULL 값이 거의 없는 열에 SPARSE 예약어를 지정해 보았다.

SPARSE 예약어를 붙인 테이블이 더 많은 공간을 차지한 것을 확인할 수 있다.

 

TRUNCATE TABLE NonSparseTbl  TRUNCATE TABLE SparseTbl  GO    DECLARE @I INT  SET @I = 0  WHILE @I < 40000  BEGIN      INSERT INTO dbo.NonSparseTbl VALUES (REPLICATE('A', 100))      INSERT INTO dbo.SparseTbl VALUES (REPLICATE('A', 100))      SET @I = @I + 1  END  GO    EXEC SP_SPACEUSED 'NonSparseTbl'  EXEC SP_SPACEUSED 'SparseTbl'  GO

 

※ reserved : 예약된 총 공간

    data : 데이터에서 사용하는 총 공간

    index_size : 인덱스에서 사용하는 총 공간

    unused : 예약되었지만 아직 사용되지 않은 총 공간

 

스파스 열은 다음과 같은 제약 사항이 있다.

  - geometry, geography, image, text, ntext, timestamp, UDT(User Define data Type)에는

    설정할 수 없다.

  - 반드시 NULL을 허용해야 하며, IDENTITY 속성을 사용할 수 없다.

  - Filestream 특성을 포함할 수 없다.

  - DEFAULT 값을 지정할 수 없다.

  - 스파스 열을 사용하면 행의 최대 크기가 8,060byte에서 8,018byte로 줄어든다.

 

위 제약 사항을 참조해서 스파스 열을 적절하게 잘 활용하면 공간 절약의 효과를 얻을 수 있다.

 

 

참고 : http://blog.sqlauthority.com/2008/07/10/sql-server-2008-introduction-to-sparse-columns/

        뇌를 자극하는 SQL Server 2008