稀疏列或 DATA_COMPRESSION？

在我的一個數據庫中，我有下表：

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應該保留哪一個？

在 MS 文件中，有一個圖表可以估計稀疏列的空間節省。

根據上述圖表，如果至少 86% 的值是 NULL，那麼您將為此列節省至少 40% 的總體成本。在這種情況下，每個TINYINT值將佔用 5 個字節，而正常的 1 個字節。

您可以通過對建議的索引使用sp_estimate_data_compression_savings將此與壓縮節省進行比較。

這應該為您提供足夠的資訊，以便您就哪種方法是正確的方法做出明智的決定。

資源：

• sp_sizeoptimiser使用統計資訊自動建議將列更改為稀疏列（如果它滿足回收至少 40% 空間的門檻值）。免責聲明：我寫的！

引用自：https://dba.stackexchange.com/questions/220476