SPARSE Column in SQL Server

Question

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?

• user507: stackoverflow.com/questions/1398453/…

Answer 1

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.