5

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?

5

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.

0

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

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.