sparse column or DATA_COMPRESSION?

Question

in one of my databases I have the following table:

CREATE TABLE [app].[applicantSkill](
 [ApplicantSkillID] [int] IDENTITY(1,1) NOT NULL,
 [applicantID] [int] NOT NULL,
 [skillID] [tinyint] NOT NULL,
 [skillDetails] [varchar](500) NULL,
 [skillLevelID] [tinyint] SPARSE NULL,
 [dateAdded] [datetime2](7) NOT NULL,
 [lastModified] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_tbl_applicant_skill] PRIMARY KEY CLUSTERED 
(
 [ApplicantSkillID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [UserFG],
 CONSTRAINT [uc_appSkillID] UNIQUE NONCLUSTERED 
(
 [applicantID] ASC,
 [skillID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [UserFG]
) ON [UserFG]
GO

which has 1 sparse column - skillLevelID

when I have to re-create my index:

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 = OFF, DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [NONCLUSTERED_INDEXES] 

I get the following error message:

Msg 10622, Level 16, State 1, Line 18
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.

How can I find out which one the sparse column or the data_compression should I keep?

• mathewb: What is your goal for using a sparse column or compression? What's the average length of the values stored in the 'skillDetails' field?

Answer 1

In the MS documentation, there is a chart to estimate space savings for sparse columns.

According to said chart, if at least 86% of the values are NULL, you will have an overall savings of at least 40% for this column. Each TINYINT value is going to take up 5 bytes versus the normal 1 byte in this scenario.

You can compare this with the compression savings by using sp_estimate_data_compression_savings for the proposed index.

This should give you enough information to make an informed decision about which is the right approach.

Resources:

• sp_sizeoptimiser Uses statistics to automatically recommend changing a column to sparse if it meets the threshold to reclaim at least 40% space. Disclaimer: I wrote it!

• Marcello Miorelli: I could not find the sp_sizeoptimiser have you changed the link?
• Marcello Miorelli: you have lots of good sql stuff, specially sql express on your page, I recommend it!
• LowlyDBA - John M: @MarcelloMiorelli Oops, looks like I need to fix that URL. Thanks for the headsup