SQL Server Full Text Search是否支持关联表？多表搜索方案咨询

针对你需要将主表多列、一对多子表列、多对一父表列纳入全文搜索范围的需求，以下是几种无需手动维护拼接表的可行方案：

1. 基于索引视图的全文索引 #

创建包含主表、父表关联列，以及子表聚合文本的索引视图，再在视图上创建全文索引。基础表数据发生Upsert时，索引视图会自动同步更新，无需手动维护额外表。

示例实现： #

假设你有三张表：

• ParentTable（父表，ParentId为主键，ParentText为搜索列）
• MainTable（主表，MainId为主键，MainCol1/MainCol2为搜索列，ParentId关联父表）
• ChildTable（子表，ChildId为主键，ChildText为搜索列，MainId关联主表）

创建索引视图：

CREATE VIEW vw_FullTextSearch
WITH SCHEMABINDING
AS
SELECT 
    m.MainId,
    m.MainCol1,
    m.MainCol2,
    p.ParentText,
    STRING_AGG(c.ChildText, ' ') AS ChildTexts
FROM dbo.MainTable m
JOIN dbo.ParentTable p ON m.ParentId = p.ParentId
LEFT JOIN dbo.ChildTable c ON m.MainId = c.MainId
GROUP BY m.MainId, m.MainCol1, m.MainCol2, p.ParentText
GO

-- 创建唯一聚集索引（索引视图必需）
CREATE UNIQUE CLUSTERED INDEX IX_vw_FullTextSearch_MainId ON vw_FullTextSearch(MainId)
GO

-- 创建全文索引
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT
GO
CREATE FULLTEXT INDEX ON vw_FullTextSearch(
    MainCol1, MainCol2, ParentText, ChildTexts
) KEY INDEX IX_vw_FullTextSearch_MainId
GO

搜索时直接查询视图：

SELECT * FROM vw_FullTextSearch
WHERE CONTAINS((MainCol1, MainCol2, ParentText, ChildTexts), '搜索关键词')

2. 多表全文索引联合查询 #

分别在主表、父表、子表的目标列上创建独立的全文索引，通过CONTAINSTABLE或FREETEXTTABLE获取匹配的记录ID，再通过JOIN或UNION合并结果，避免全表关联拼接的性能损耗。

示例实现： #

先给各表创建全文索引：

-- 父表全文索引
CREATE FULLTEXT INDEX ON ParentTable(ParentText) KEY INDEX PK_ParentTable_ParentId
-- 主表全文索引
CREATE FULLTEXT INDEX ON MainTable(MainCol1, MainCol2) KEY INDEX PK_MainTable_MainId
-- 子表全文索引
CREATE FULLTEXT INDEX ON ChildTable(ChildText) KEY INDEX PK_ChildTable_ChildId

搜索查询（获取所有匹配关键词的主表记录）：

SELECT DISTINCT m.*
FROM MainTable m
-- 匹配主表自身列
JOIN CONTAINSTABLE(MainTable, (MainCol1, MainCol2), '搜索关键词') ct1 ON m.MainId = ct1.[KEY]
UNION
SELECT DISTINCT m.*
FROM MainTable m
-- 匹配父表列
JOIN ParentTable p ON m.ParentId = p.ParentId
JOIN CONTAINSTABLE(ParentTable, ParentText, '搜索关键词') ct2 ON p.ParentId = ct2.[KEY]
UNION
SELECT DISTINCT m.*
FROM MainTable m
-- 匹配子表列
JOIN ChildTable c ON m.MainId = c.MainId
JOIN CONTAINSTABLE(ChildTable, ChildText, '搜索关键词') ct3 ON c.ChildId = ct3.[KEY]

这种方式无需额外维护对象，适合搜索需求灵活多变的场景，但查询语句相对复杂。

3. 主表+计算列结合子表索引视图 #

对于主表和父表的关联列，可以在主表中创建带SCHEMABINDING的计算列，直接拼接主表列和父表列，然后对计算列创建全文索引；子表部分仍用索引视图聚合文本，搜索时关联主表和子表视图。

示例实现： #

-- 在主表添加计算列
ALTER TABLE MainTable
ADD CombinedMainParentText AS CONCAT(MainCol1, ' ', MainCol2, ' ', (SELECT ParentText FROM ParentTable p WHERE p.ParentId = MainTable.ParentId))
WITH SCHEMABINDING
GO

-- 对计算列创建全文索引
CREATE FULLTEXT INDEX ON MainTable(CombinedMainParentText) KEY INDEX PK_MainTable_MainId
GO

-- 子表索引视图（同方案1）
CREATE VIEW vw_ChildTextAgg
WITH SCHEMABINDING
AS
SELECT MainId, STRING_AGG(ChildText, ' ') AS ChildTexts
FROM dbo.ChildTable
GROUP BY MainId
GO
CREATE UNIQUE CLUSTERED INDEX IX_vw_ChildTextAgg_MainId ON vw_ChildTextAgg(MainId)
GO
CREATE FULLTEXT INDEX ON vw_ChildTextAgg(ChildTexts) KEY INDEX IX_vw_ChildTextAgg_MainId
GO

-- 搜索查询
SELECT DISTINCT m.*
FROM MainTable m
LEFT JOIN vw_ChildTextAgg c ON m.MainId = c.MainId
WHERE CONTAINS(m.CombinedMainParentText, '搜索关键词')
OR CONTAINS(c.ChildTexts, '搜索关键词')

方案对比 #

• 索引视图方案：维护成本低，搜索查询简单，但索引视图会占用额外存储空间，数据变更频繁时可能带来一定的写性能损耗。
• 多表联合查询方案：无需额外存储，灵活性高，但查询语句复杂，多次UNION可能增加查询开销。
• 计算列+索引视图方案：拆分了主父表和子表的搜索逻辑，适合主父表关联稳定、子表数据变化频繁的场景。

内容的提问来源于stack exchange，提问作者David Thielen