SQL Server按非主键分区后全文索引创建问题求助

问题背景 #

使用SQL Server 2016及以上版本，需对Products表按非主键字段DivisionID分区，以优化数据导入和磁盘分布。但该表主键为复合键(DivisionID, ID)（ID是自增列），而全文索引要求单列唯一键，直接创建全文索引会报错；同时创建单列唯一索引又因分区规则要求索引包含分区列而失败。

问题重现代码 #

DROP TABLE IF EXISTS dbo.Products
BEGIN TRY DROP PARTITION SCHEME division_id_scheme      END TRY BEGIN CATCH END CATCH
BEGIN TRY DROP PARTITION FUNCTION division_id_function  END TRY BEGIN CATCH END CATCH
BEGIN TRY DROP FULLTEXT CATALOG ProductNames            END TRY BEGIN CATCH END CATCH

CREATE PARTITION FUNCTION division_id_function ( INT )  AS RANGE LEFT FOR VALUES ( 1, 2, 3 )
CREATE PARTITION SCHEME division_id_scheme AS PARTITION division_id_function ALL TO ( [PRIMARY] )

CREATE TABLE dbo.Products ( ID           INT           IDENTITY(1, 1)
,                           Catalogueref NVARCHAR(50)  NOT NULL
,                           ProductName  NVARCHAR(255) NOT NULL
,                           DivisionID   INT           NOT NULL
,                           CONSTRAINT pk_Products PRIMARY KEY ( DivisionID, ID )) ON division_id_scheme (DivisionID);

INSERT INTO dbo.Products ( Catalogueref, ProductName, DivisionID )
VALUES ( N'ABC123', N'Product Apple', 1 )
,      ( N'ABC124', N'Product Ball', 1 )
,      ( N'ABC125', N'Product Cat', 2 )
,      ( N'ABC126', N'Product Dog', 2 )
,      ( N'ABC127', N'Product Egg', 3 )
,      ( N'ABC128', N'Product Fox', 3 )
,      ( N'ABC129', N'Product Grape', 3 )

SELECT * FROM  dbo.Products

创建全文索引的错误 #

执行以下语句会触发报错：

CREATE FULLTEXT CATALOG ProductNames

-- 此语句失败，因为pk_Products不是单列唯一索引
CREATE FULLTEXT INDEX ON dbo.Products (ProductName) KEY INDEX pk_Products ON ProductNames

错误信息：

Msg 7653, Level 16, State 2, Line 29
'pk_Products' 不是有效的全文搜索键索引。全文搜索键必须是唯一、不可为空的单列索引，且不能处于离线状态，不能定义在非确定性或不精确的非持久化计算列上，不能有筛选器，最大大小为900字节。请选择另一个索引作为全文键。

创建单列唯一索引的错误 #

尝试创建单列唯一索引同样失败：

-- 此语句失败，因为唯一索引必须是（复合）索引的子集
DROP INDEX IF EXISTS ui_CatRef ON dbo.Products
CREATE UNIQUE NONCLUSTERED INDEX ui_CatRef ON dbo.Products (Catalogueref)

可行解决方案 #

核心思路：创建包含分区列的非聚集单列唯一索引，同时满足全文索引的键要求与分区表的索引规则。

具体实现代码 #

-- 删除已存在的无效索引（若有）
DROP INDEX IF EXISTS ui_Products_ID ON dbo.Products

-- 创建基于ID的非聚集唯一索引，包含DivisionID列以满足分区规则
CREATE UNIQUE NONCLUSTERED INDEX ui_Products_ID ON dbo.Products (ID)
INCLUDE (DivisionID)
ON division_id_scheme(DivisionID); -- 绑定到分区方案，确保索引与表分区对齐

-- 创建全文目录（若未创建）
CREATE FULLTEXT CATALOG ProductNames

-- 使用新的单列唯一索引创建全文索引
CREATE FULLTEXT INDEX ON dbo.Products (ProductName) 
KEY INDEX ui_Products_ID ON ProductNames;

方案说明 #

• 非聚集唯一索引ui_Products_ID以自增列ID作为唯一键列，天然满足全文索引对单列唯一键的要求。
• 通过INCLUDE (DivisionID)将分区列包含在索引中，同时将索引绑定到分区方案，符合SQL Server分区表的规则：非聚集索引需与表分区对齐，或包含分区列。
• 该方案同时满足按DivisionID分区的需求与全文索引的配置要求。

验证 #

执行以下语句测试全文索引功能：

SELECT * FROM dbo.Products
WHERE CONTAINS(ProductName, 'Apple');

内容的提问来源于stack exchange，提问作者Matthew Baker