SQL Server中位掩码类颜色数据建模与高效查询方案咨询

现有SQL Server表结构如下：

其中Color字段为最多包含16种颜色的JSON数组（类似枚举位掩码）。需执行如select * from table where Color contains 'Green' and/or Color contains 'Blue'的查询。此前使用全文索引在物理表上表现良好，但基于该表的视图无法复用表的全文索引，需额外创建。现寻求无需创建额外表和关联操作的重新建模及索引方案，以实现数据的快速检索。

重新建模：整数位掩码存储 #

因为颜色最多16种，刚好可以用smallint类型（16位整数）存储位掩码，每种颜色对应一个二进制位：

• 给每种颜色分配唯一位值：比如Blue=1（2⁰）、Green=2（2¹）、Red=4（2²）、Yellow=8（2³）……第16种颜色对应2¹⁵=32768
• 存储时，将记录包含的颜色位值相加：比如包含Blue+Green+Yellow的记录，Color字段值为1+2+8=11

查询方式 #

• 同时包含Green和Blue：SELECT * FROM table WHERE (Color & 3) = 3（3是1|2，即两种颜色的位掩码组合）
• 包含Green或Blue：SELECT * FROM table WHERE (Color & 3) > 0
• 仅包含Blue：SELECT * FROM table WHERE Color = 1

索引方案 #

直接给Color字段创建非聚集索引，视图可直接复用该索引：

CREATE NONCLUSTERED INDEX IX_Color ON [table](Color);

兼容原有JSON格式（可选） #

如果需要保留原格式展示，可添加持久化计算列：

ALTER TABLE [table] ADD Color_JSON AS (
    STUFF(
        CONCAT(
            CASE WHEN (Color & 1) = 1 THEN ', Blue' ELSE '' END,
            CASE WHEN (Color & 2) = 2 THEN ', Green' ELSE '' END,
            CASE WHEN (Color & 4) = 4 THEN ', Red' ELSE '' END,
            CASE WHEN (Color & 8) = 8 THEN ', Yellow' ELSE '' END
            -- 依次补充其他颜色的判断逻辑
        ), 1, 2, ''
    )
) PERSISTED;

内容的提问来源于stack exchange，提问作者xon.ha