Is there some drawback to define SPARSE columns?

Question

I have a table which has a lot of NULL values in columns. But some columns don't contain NULLs at all (although nullable). Is there some drawback to declare all of these columns as SPARSE?

Answer 1

Columns which are SPARSE but don't have NULL values in it will take up more space than a non-SPARSE column. You'll only want to use a SPARSE column when the data is actually sparse.

Answer 2

the greatest draw back for me comes to this error message below:

A compressed index is not supported on table that contains sparse columns or a column set column

--Msg 10622, Level 16, State 1, Line 15
--The index 'I_applicantID' could not be created or rebuilt. A compressed index is not supported on table that contains sparse columns or a column set column.
CREATE NONCLUSTERED INDEX I_applicantID  ON [app].[applicantSkill] (  [applicantID] ASC  , [dateAdded] ASC  )  INCLUDE ( [ApplicantSkillID] , [skillDetails] , [skillID] , [skillLevelID])  
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, DROP_EXISTING = ON, DATA_COMPRESSION=NONE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [NONCLUSTERED_INDEXES] 

when trying to apply DATA_COMPRESSION=PAGE to a nonclustered index.

you can have either a sparser column or data compression on a table. to find out which one would suit you better for each table check out this answer.

Answer 3

They prevent some online operations.

In this case, the sparse columns feature prevents us from adding new non-null columns with a default value as an online, metadata-only change.

https://meta.stackexchange.com/questions/376015/planned-maintenance-scheduled-for-saturday-february-19-2022-at-200am-utc-fri