2

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?

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

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!
  • I could not find the sp_sizeoptimiser have you changed the link? – Marcello Miorelli Nov 24 at 17:41
  • you have lots of good sql stuff, specially sql express on your page, I recommend it! – Marcello Miorelli Nov 24 at 17:49
  • 1
    @MarcelloMiorelli Oops, looks like I need to fix that URL. Thanks for the headsup – LowlyDBA Nov 25 at 15:04

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.