Sql-server – SPARSE Column in SQL Server

sparse-columnsql server

I have just learned about SPARSE columns in SQL Server. I have never used them; I've just read about it on the Internet.

Can I ALTER an existing Nullable column in large transactional tables to take advantage of the SPARSE property? Would it be beneficial for space and performance?

Best Answer

You could test space-reduction using something like:

USE tempdb;
CREATE TABLE xc
(
    x INT NULL
);

INSERT INTO xc (x) VALUES (CASE WHEN (RAND() * 10) < 5 THEN NULL ELSE (RAND() * 10) END);
GO 10000

SELECT o.name, SUM(au.used_pages) * 8192 / 1048576e0
FROM sys.allocation_units au
    INNER JOIN sys.partitions p ON au.container_id = p.hobt_id
    INNER JOIN sys.objects o on o.object_id = o.object_id
WHERE o.name = 'xc' AND (au.type = 1 or au.type = 3)
GROUP BY o.name;

SELECT * FROM dbo.xc

ALTER TABLE xc ALTER COLUMN x INT SPARSE NULL;
ALTER TABLE xc REBUILD;

SELECT o.name, SUM(au.used_pages) * 8192 / 1048576e0
FROM sys.allocation_units au
    INNER JOIN sys.partitions p ON au.container_id = p.hobt_id
    INNER JOIN sys.objects o on o.object_id = o.object_id
WHERE o.name = 'xc' AND (au.type = 1 or au.type = 3)
GROUP BY o.name;

Running this drops the number of pages used by the table.

Related Question