SQL Server ->> Computed Column计算列于Filter Index过滤索引的冲突

今天本来想对一张数仓表做一个性能优化，方法是通过对一个常用的WHERE条件（COL1 IS NULL OR COLB = 0）创建一个计算列，然后对计算列添加索引从而优化性能。

ALTER TABLE [dbo].[FactTable]
ADD COMPCOL_FOR_INDEX_COL3 AS CASE WHEN COL1 IS NULL OR COLB = 0 THEN 1 ELSE 0 END PERSISTED
GO

CREATE NONCLUSTERED INDEX IX_FactTable_COMPCOL_FOR_INDEX_COL3 ON [dbo].[FactTable](COMPCOL_FOR_INDEX_COL3)
WHERE COMPCOL_FOR_INDEX_COL3 = 1
WITH(DATA_COMPRESSION=PAGE)
GO

 

但是上面的语句报错了

Msg 10609, Level 16, State 1, Line 9
Filtered index 'IX_FactTable1_COMPCOL_FOR_INDEX_COL3' cannot be created on table 'dbo.FactTable' because the column 'COMPCOL_FOR_INDEX_COL3' in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.

 

原来SQL SERVER 不允许对计算列创建过滤索引，查了一下谷歌才知道，这个问题早在2009年就有人去microsoft connect给微软提了ticket，但是都1202年了，微软也没有计划优化这一点。那就没办法了。只好改掉语句。

ALTER TABLE [dbo].[FactTable]
ADD COMPCOL_FOR_INDEX_COL3 AS CASE WHEN COL1 IS NULL OR COLB = 0 THEN 1 ELSE 0 END PERSISTED
GO

CREATE NONCLUSTERED INDEX IX_FactTable_COMPCOL_FOR_INDEX_COL3 ON [dbo].[FactTable](COMPCOL_FOR_INDEX_COL3 DESC)
WITH(DATA_COMPRESSION=PAGE)
GO