在 SQL Server 中对多个表进行全文搜索失败

tl;dr 版本：使用 FREETEXTABLE 的全文搜索在一个表中的一列上运行良好，当遵循相同的步骤但全文索引位于视图中的多个列时，会产生 100% 的假阳性和 100% 的假阴性由两个基表（原始表和一个新表）组成。研究揭示了相互矛盾的说法，即如果将多个表连接到一个视图中，则可以针对多个表运行全文搜索，并且在查询视图时搜索只能涉及单个基表。

我们正在运行 SQL Server 2008 R2。作为一个更大项目的一部分，我们正在为客户创建一个可搜索的食谱数据库。客户提供了从第三方购买的 XML 格式的数据。基于应用程序的线框图，我们导入了数据以及哪些数据是相关的。相关部分的 DDL 和数据的子集：

CREATE TABLE recipe (
    id INT IDENTITY(1,1) NOT NULL,
    title NVARCHAR(255) NOT NULL,
    descrip NVARCHAR(4000) NOT NULL,
    prep_time NVARCHAR(127),
    ease_of_prep NVARCHAR(25)
    CONSTRAINT PK_recipe PRIMARY KEY CLUSTERED (id)
);

INSERT INTO recipe (title, descrip, prep_time, ease_of_prep)
VALUES ('Aromatic Rice Pudding', 'Store-bought rice pudding with some simple stir-ins.', '5 minutes', 'Easy'),
    ('Lemon Chicken Stir-Fry', 'Spiked with lots of zesty lemon, this stir-fry has a colorful mix of snow peas, carrots and scallions. Substitute any thinly sliced vegetables like bell peppers or celery.', '40 minutes', 'Easy'),
    ('Salsa-Roasted Salmon', 'Fire up the food processor, add a few simple ingredients, and you’ve got a vibrant-tasting salsa in minutes. Substitute other fish, chicken or turkey for the salmon—adjust the roasting time accordingly.', '10 minutes', 'Easy');

我没有被告知搜索标准是什么，但假设有多个字段，并且全文会产生更符合客户正在寻找的结果。FTC 和全文索引的 DDL：

CREATE FULLTEXT CATALOG [ftc_default]WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION [dbo];

CREATE FULLTEXT INDEX ON recipe KEY INDEX PK_recipe ON (ftc_default) WITH (CHANGE_TRACKING AUTO);

ALTER FULLTEXT INDEX ON recipe ADD title;
ALTER FULLTEXT INDEX ON recipe ENABLE;

我编写了以下存储过程（可选但对测试有用）：

CREATE PROCEDURE test_fulltext
    @terms NVARCHAR(2000) = NULL

AS
BEGIN

    IF ISNULL(@terms, '') = ''

        SELECT id, title, descrip
        FROM recipe;

    ELSE
        SELECT r.id, r.title, r.descrip
        FROM recipe r 
        INNER JOIN FREETEXTTABLE(recipe, title, @terms) kt
            ON r.idid = kt.[KEY]
        ORDER BY RANK DESC;

END

这如宣传的那样工作。搜索“辣椒调味莎莎”将返回大约十几个食谱，其中“辣椒调味牛排和潘莎莎”作为顶级食谱，其他相关性降低到“辣椒粉调味鸡肉”。单看标题，似乎没有误报。

我更新了项目经理，让他知道食谱可以在标题上搜索，如果客户想要包含任何其他字段，我可以添加它们。然后他问我们为什么不搜索美食类型、主题、健康考虑和其他。啊。我们没有被告知要导入这些数据。回到原点。

如果不对其进行非规范化，其他数据将无法放入配方表中，因此我们将其导入到新表中。整个 DDL 和数据子集：

CREATE TABLE recipe_search (
    ID INT IDENTITY(1,1) NOT NULL,
    recipe_id INT NOT NULL,
    term_type NVARCHAR(25) NULL,
    term_value NVARCHAR(50) NULL
    CONSTRAINT PK_recipe_search PRIMARY KEY CLUSTERED (id)
);

ALTER TABLE recipe_search 
WITH CHECK ADD CONSTRAINT FK_recipe_recipe_id 
FOREIGN KEY (recipe_id) REFERENCES recipe (id);

INSERT INTO recipe-search (recipe_id, term_type, term_value)
VALUES (1, 'Course', 'Dessert'),
    (1, 'Course', 'Snacks'),
    (1, 'Cuisine', 'Middle Eastern'),
    (1, 'Cusines', 'Mediterranean'),
    (1, 'Dish Type', 'Desserts'),
    (1, 'Health Consideration', 'Healthy Weight'),
    (1, 'Season', 'Winter'),
    (1, 'Season', 'Fall'),
    (1, 'Style/Theme', 'Vegetarian'),
    (1, 'Technique', 'No Cook'),
    (2, 'Course', 'Dinner'),
    (2, 'Cuisine', 'Asian'),
    (2, 'Dish Type', 'Main Dish'),
    (2, 'Health Consideration', 'Low Calorie'),
    (2. 'Health Consideration', 'Low Carb'),
    (2, 'Main Ingredient', 'Chicken'),
    (2, 'Technique', 'Stir-fry'),
    (3, 'Course', 'Dinner'),
    (3, 'Cuisine', 'Southwestern'),
    (3, 'Cuisine', 'Mexican'),
    (3, 'Cuisine', 'American'),
    (3, 'Dish Type', 'Main Dish'),
    (3, 'Health Consideration', 'Low Carb'),
    (3, 'Season', 'Summer'),
    (3, 'Technique', 'Bake'),
    (3, 'Roast', 'Food Processor');

然后，阅读全文搜索只能在一个表上工作，并且要搜索多个表必须创建一个视图，我这样做了：

CREATE VIEW dbo.vw_recipe_search
WITH SCHEMABINDING
AS
    SELECT rs.id search_id, r.id recipe_id, r.title, r.descrip, rs.term_value, r.ease_of_prep
    FROM dbo.recipe r
    INNER JOIN dbo.recipe_search rs ON r.id = rs.recipe_id
    WHERE r.active = 1;
GO

CREATE UNIQUE CLUSTERED INDEX idx_searchid ON vw_recipe_search (search_id);
GO

CREATE FULLTEXT INDEX ON vw_recipe_search
KEY INDEX idx_searchid ON (ftc_fu_default) WITH (CHANGE_TRACKING AUTO);
GO

ALTER FULLTEXT INDEX ON vw_recipe_search ADD (descrip);
GO
ALTER FULLTEXT INDEX ON vw_recipe_search ADD (term_value);
GO
ALTER FULLTEXT INDEX ON vw_recipe_search ADD (title);
GO
ALTER FULLTEXT INDEX ON vw_recipe_search ADD (ease_of_prep);
GO
ALTER FULLTEXT INDEX ON vw_recipe_search ENABLE;

并更改了 SP（编辑：我更新了下面的代码以指示我所做的实际更改）：

ALTER PROCEDURE test_fulltext
    @terms NVARCHAR(2000) = NULL

AS
BEGIN

    IF ISNULL(@terms, '') = ''

        SELECT id, title, descrip
        FROM recipe;

    ELSE
        SELECT v.*
    FROM vw_recipe_search v 
    INNER JOIN FREETEXTTABLE(vw_recipe_search, (title, descrip, term_value, ease_of_prep), @terms) kt
        ON v.recipe_id = kt.[KEY]
    ORDER BY v.recipe_id DESC;

END

现在搜索的结果完全是胡说八道。例如，搜索“salsa”会找到五个独特的食谱，其中没有一个包含“salsa”一词或任何索引字段中任何合理的同义词同义词；没有找到与莎莎相关的菜肴类型。（其中一个点击确实包含单词“sauce”，我想这可能是“salsa”的同义词。）五个食谱中的两个是上面数据集中的前两个食谱。同时，搜索不会返回salsa-roasted三文鱼或任何其他包含“salsa”一词的食谱。我已经验证了全文目录和索引都按预期创建，并且全文目录中的项目数与视图返回的行数相同（大约 4000，

另一个烦恼：如果配方符合标准，则视图中的所有记录都会被选中。几乎就好像它始终是导致命中的表 recipe 中的列之一，因此 recipe_search 中的所有连接记录都包括在内。

在研究这个奇怪的反转时，我发现了以下链接：“查询视图时，只能涉及一个全文索引基表。”

问题#1：哪个是正确的——你能不能在全文搜索中查询多个表？
问题 #2：我在尝试创建多表全文搜索时是否遗漏了什么？如果没有，这里可能存在什么问题？