SQL全文搜索函数首次运行无结果问题排查

• 搭建了SQL Server报表自定义对象识别体系：提取二进制存储的RDL报表数据集CommandText为VARCHAR(MAX)存储，维护自定义数据库对象表，通过自定义函数调用CONTAINS全文搜索识别CommandText中的对象引用。
• 故障：单脚本执行（含建表、填数据、创建全文目录/索引、调用UPDATE更新报表表）时，自定义函数未返回预期结果；手动单独执行UPDATE语句则结果正确，已用GO拆分批次无改善。

创建全文索引后，SQL Server默认异步后台执行索引填充，脚本中紧接着执行UPDATE时，全文索引尚未完成数据同步，导致CONTAINS查询无法匹配到数据。GO仅分隔执行批次，不会等待后台异步操作完成，因此无法解决问题。

方案1：强制触发全文索引全量填充并等待完成 #

在创建全文索引的代码之后，添加手动启动填充并循环检查填充状态的逻辑，确保索引就绪后再执行UPDATE：

-- 创建全文索引部分（原代码保留）
CREATE UNIQUE CLUSTERED INDEX idx_ID ON dbo.tmpPremRptCmdTxt (ID)

CREATE FULLTEXT CATALOG Prem_tmpPremRptCmdTxt_FTCat 
CREATE FULLTEXT INDEX ON tmpPremRptCmdTxt
(
    DataSet_CommandText            
    Language 1033                 
)  
    KEY INDEX idx_ID  
    ON Prem_tmpPremRptCmdTxt_FTCat  
    WITH CHANGE_TRACKING OFF

-- 新增：启动全文索引全量填充
ALTER FULLTEXT INDEX ON tmpPremRptCmdTxt START FULL POPULATION;

-- 新增：等待填充完成（先延迟5秒再循环检查，避免频繁查询系统视图）
WAITFOR DELAY '00:00:05';

WHILE EXISTS (
    SELECT 1 
    FROM sys.fulltext_indexes fi
    JOIN sys.tables t ON fi.object_id = t.object_id
    WHERE t.name = 'tmpPremRptCmdTxt'
    -- 1=正在填充, 3=正在重建,4=正在增量填充：这些状态表示填充未完成
    AND fi.population_status IN (1, 3, 4)
)
BEGIN
    WAITFOR DELAY '00:00:02'; -- 每2秒检查一次状态
END
GO

-- 执行UPDATE更新报表表（原代码保留）
UPDATE tmpPremRptCmdTxt SET [CustomObjectsUsedInCommandText] = ISNULL(CustRptObjs.[CustomObjectsLocated], '')
FROM tmpPremRptCmdTxt 
LEFT JOIN (
    SELECT 
        ResultSet.ReportID, ResultSet.DatasetID
        , STRING_AGG('[' + CONVERT(VARCHAR(MAX), ResultSet.CustomObjects) + '] (' + ResultSet.ObjectType + ')', '; ') as [CustomObjectsLocated]
    FROM (
        SELECT 
            Rpt.ReportID, Rpt.DatasetID, Cob.ObjectType
            , dbo.[fnPremFTSearch_v2](MAX(Rpt.ID), Rpt.ReportID, Rpt.DatasetID, Cob.ObjectName) as CustomObjects
        FROM tmpPremRptCmdTxt Rpt
        OUTER APPLY         
        (
            SELECT DISTINCT ObjectName, ObjectType 
            FROM tmpPremCustomObjects       
        ) Cob
        WHERE ISNULL(Rpt.DataSet_CommandText, '') <> ''     
        GROUP BY Rpt.ReportName, Rpt.ReportID, Rpt.DatasetID, Cob.ObjectName,ObjectType
        HAVING dbo.[fnPremFTSearch_v2](MAX(Rpt.ID), Rpt.ReportID, Rpt.DatasetID, Cob.ObjectName) IS NOT NULL
    ) ResultSet
    GROUP BY ResultSet.ReportID, ResultSet.DatasetID
) CustRptObjs ON CustRptObjs.ReportID = tmpPremRptCmdTxt.ReportID
    AND CustRptObjs.DatasetID = tmpPremRptCmdTxt.DatasetID
GO

方案2：调整全文索引创建时的填充模式（可选） #

如果需要更严格的同步控制，可以在创建全文索引时指定POPULATION ON CREATE，但SQL Server仍可能异步执行，因此建议结合方案1的等待逻辑确保可靠：

CREATE FULLTEXT INDEX ON tmpPremRptCmdTxt
(
    DataSet_CommandText            
    Language 1033                 
)  
    KEY INDEX idx_ID  
    ON Prem_tmpPremRptCmdTxt_FTCat  
    WITH CHANGE_TRACKING OFF, POPULATION ON CREATE;

补充说明 #

• 自定义函数中同时使用CONTAINS和LIKE的逻辑是合理的：CONTAINS利用全文索引提升性能并实现精确词匹配，LIKE用于排除包含目标字符串但并非完整对象名的场景（如EmployeeDetail$不会匹配EmployeeDetail）。
• 若数据集规模较小，也可考虑暂时替换全文搜索为纯LIKE查询（去掉CONTAINS），但大规模数据下性能会显著下降，不推荐长期使用。

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