Is there some drawback to define SPARSE columns?

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

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

enter image description here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply