Falha na pesquisa de texto completo no SQL Server em várias tabelas

versão tl;dr: Uma pesquisa de texto completo usando FREETEXTABLE que funcionou bem em uma coluna em uma tabela produz 100% de falsos positivos e 100% de falsos negativos quando as mesmas etapas são seguidas, mas o índice de texto completo está em várias colunas em uma exibição composta por duas tabelas base (a tabela original e uma nova). A pesquisa revela alegações contraditórias de que uma pesquisa de texto completo pode ser executada em várias tabelas se elas forem unidas em uma exibição e que a pesquisa só pode envolver uma única tabela base quando uma exibição é consultada.

Estamos executando o SQL Server 2008 R2. Como parte de um projeto maior, estamos criando um banco de dados pesquisável de receitas para um cliente. O cliente forneceu dados em formato XML adquiridos de terceiros. Com base no wireframe do aplicativo, importamos os dados e quais dados eram relevantes. DDL na parte pertinente e um subconjunto dos dados:

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');

Não me disseram quais seriam os critérios de pesquisa, mas assumi mais de um campo e que o texto completo produziria resultados mais sintonizados com o que o cliente estava procurando. DDL para FTC e índice de texto completo:

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;

Escrevi o seguinte procedimento armazenado (opcional, mas útil para teste):

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

Isso funcionou como anunciado. Uma pesquisa sobre 'salsa com pimenta' retornaria cerca de uma dúzia de receitas, com "Bifes com pimenta e molho pan" como a receita principal e outras com relevância decrescente até "Frango com páprica com ervas". Apenas olhando os títulos, parecia não haver falsos positivos.

Atualizei o gerente de projeto para que ele soubesse que as receitas podiam ser pesquisadas pelo título e, se o cliente quisesse incluir outros campos, eu poderia adicioná-los. Ele então perguntou por que não estávamos pesquisando por tipo de cozinha, tema, considerações de saúde e outros. Eca. Não nos disseram para importar esses dados. De volta à estaca zero.

Os outros dados não caberiam na tabela da receita sem desnormalizá-la, então nós os importamos para uma nova tabela. DDL inteiro e um subconjunto de dados:

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');

Então, tendo lido que a pesquisa de texto completo só pode funcionar em uma tabela e que para pesquisar várias tabelas é necessário criar uma exibição, fiz isso:

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;

E alterei o SP ( EDIT : atualizei o código abaixo para indicar as alterações reais que fiz):

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

E agora os resultados da pesquisa são um absurdo completo. Uma pesquisa sobre "salsa", por exemplo, encontra cinco receitas exclusivas, nenhuma das quais contém a palavra "salsa" ou qualquer sinônimo razoável de dicionário de sinônimos em qualquer um dos campos indexados; nenhum dos tipos de pratos que se associa à salsa é encontrado. (Um dos resultados contém a palavra "molho", que suponho que possa ser um sinônimo de dicionário de sinônimos para "salsa".) Duas das cinco receitas são as duas primeiras receitas no conjunto de dados acima. Enquanto isso, nem o salmão assado na salsa, nem qualquer outra receita que contenha a palavra "salsa", é retornado pela pesquisa. Verifiquei que o catálogo de texto completo e o índice foram criados conforme o esperado e que o catálogo de texto completo tem o mesmo número de itens que o número de linhas retornadas da exibição (cerca de 4.000,

Um aborrecimento adicional: se uma receita atender aos critérios, TODOS os seus registros na exibição serão selecionados. É quase como se fosse sempre uma das colunas na receita da tabela que leva à ocorrência, portanto, todos os registros unidos na receita_pesquisa são incluídos.

Ao examinar essa reversão bizarra, encontrei o seguinte link : "Ao consultar uma exibição, apenas uma tabela base indexada de texto completo pode ser envolvida."

Pergunta nº 1: Qual é a correta - você pode ou não consultar várias tabelas em uma pesquisa de texto completo?
Pergunta nº 2: Deixei algo de fora ao tentar criar uma pesquisa de texto completo em várias tabelas e, se não, qual pode ser o problema aqui?