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?
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.
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.