Sql-server – how to find tables with either sparse or a column set column

Rebuild the index to write out all pages freshly in the optimal way.

If you want to absolutely minimize space usage, specify ONLINE = OFF. Online operations add a small amount of per-row space overhead.

MAXDOP = 1 can be good to reduce fragmentation. Not sure if that can help with space usage, though. I can't think of anything that a DOP of 1 might save.

If the target file of the rebuild already contains fragmented freespace the newly built index/partition can turn out to be fragmented immediately. Consider building into an empty target filegroup that is pre-sized to approximately the right size.

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.