稀疏列还是DATA_COMPRESSION？

Question

在我的一个数据库中，我有下表：

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

其中有1 稀疏柱 - skillLevelID

当我不得不重新创建索引时：

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] 

我收到以下错误消息：

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.

如何找出我应该保留哪一个the sparse column or the data_compression？

Answer 1

在MS文档中，有图表来估计稀疏列节省的空间。

根据上述图表，如果至少86%的值为空，则本专栏的总体储蓄至少为40%。在这个场景中，每个TINYINT值将占用5个字节而不是普通的1个字节。

通过对建议的索引使用服务提供商_估计值_数据_压缩_储蓄，您可以将其与压缩节省进行比较。

这将为您提供足够的信息，以便对哪一种方法做出明智的决定。

资源：

• 服务提供商_尺寸优化器使用统计信息自动建议将列更改为稀疏，如果该列满足至少40%的空间要求。免责声明:我写的！