1

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?

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.