[DBMS] 스파스 열(Sparse Column)

Dec 28, 2024

💡

스파스 열(Sparse Column)은 ‘NULL값에 대해 최적화된 저장소가 있는 일반 열’로 정의할 수 있다. 즉 NULL 값이 많이 들어갈 것으로 예상되는 열이라면 스파스 열로 지정해 놓을 경우 많은 공간 절약 효과를 볼 수 있다. 그러나 NULL 값이 별로 없는 열이라면 그 크기가 더 필요해진다.

스파스 열로 지정하는 방법은 CREATE TABLE 또는 ALTER TABLE로 열을 정의할 때 뒤에 ‘SPARSE’ 명령어만 사용해주면 된다.


USE tempdb;

CREATE DATABASE sparseDB;

데이터베이스를 생성한다.


USE sparseDB;

CREATE TABLE  charTbl(
	id int IDENTITY, 
	data CHAR(100) NULL
	);

CREATE TABLE sparseCharTbl(
	id int IDENTITY,
	data CHAR(100) SPARSE NULL
	);

테이블을 생성한다. sparseCharTbl의 data 컬럼은 SPARSE 명령어를 사용한다.


DECLARE @i INT = 0
WHILE @i < 10000
BEGIN
	INSERT INTO charTbl VALUES	(null);
	INSERT INTO charTbl VALUES	(null);
	INSERT INTO charTbl VALUES	(null);
	INSERT INTO charTbl VALUES	(REPLICATE('A',100));
	INSERT INTO sparseCharTbl VALUES (null);
	INSERT INTO sparseCharTbl VALUES (null);
	INSERT INTO sparseCharTbl VALUES (null);	
	INSERT INTO sparseCharTbl VALUES (REPLICATE('A',100));
	SET @i += 1
END

반복문으로 4만 건을 대입한다. 단 data 열은 75%를 NULL 값으로 입력한다.

charTbl 와 sparseCharTbl 에 들어간 데이터는 동일하다. 두 테이블의 저장 공간 크기를 비교해보자.

개체 탐색기 > DB > 테이블 마우스 우클릭 > 속성을 선택한다.

charTbl

sparseCharTbl

속성 > 스토리지의 데이터 공간을 확인할 수 있다.

결과는 charTbl 의 저장 공간보다 sparseCharTbl 의 저장 공간이 더 절약된 것을 확인할 수 있다.


TRUNCATE TABLE charTbl;   -- 테이블은 남기고 데이터 전부 삭제
TRUNCATE TABLE sparseCharTbl;

DECLARE @i INT = 0
WHILE @i < 40000
BEGIN

	INSERT INTO charTbl VALUES	(REPLICATE('A',100));
	INSERT INTO sparseCharTbl VALUES (REPLICATE('A',100));
	SET @i += 1
END

이번에는 4만 건의 데이터 모두 NOT NULL 값을 입력한다.

charTbl

sparseCharTbl

결과를 확인했을 때 charTbl 은 NULL값이 있든 없든 크기의 차이가 없다.

하지만 sparseCharTbl은 NULL이 없어지자 오히려 크기거 더 커진 것을 확인할 수 있다.

즉 NULL 값이 별로 없는 경우는 스파스 열로 지정하면 오히려 공간을 낭비하게 된다.

💡

또한 SELECT로 해당 열을 조회할 때, 스파스 열로 지정되어 있을 경우 오히려 검색 속도가 느려진다. 그러므로 공간 절약이 약 40% 는 되는 경우에만 스파스 열로 지정하는데 의미가 있다.

아래의 표는 스파스 열로 지정할 때 약 40%의 공간 절약 효과를 보려면 각 데이터 형식별로 몇 %의 NULL 값이 있어야 하는지 나타낸다.

데이터형식	NULL 비율	데이터 형식	NULL 비율
bit	98%	date	69%
smallint	86%	datetime2	67%
int	64%	varchar	60%
bigint	52%	char	60%
float	52%	nvarchar	60%
datetime	52%	nchar	60%