mdoyle's questions

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：我在尝试创建多表全文搜索时是否遗漏了什么？如果没有，这里可能存在什么问题？

如果可能的话，我希望在特定的存储过程中替换我的基于游标的解决方案。如果有任何区别，则它在 SQL Server 2008 R2 上运行。我正在寻找一种算法而不是精确的代码。

背景：

SP 是通过直邮或电子邮件发送邮件的公司系统的一部分。邮件包含一个个性化代码，收件人可以在访问商家时输入该代码以获得特别折扣或优惠。跟踪代码使用情况并向商家提供有关对各种邮件的响应的汇总报告。“客户”被定义为具有唯一名字、姓氏和地址的邮件的目标；如果没有地址，则电子邮件替换地址。

有争议的表格如下（简化版）：

CREATE TABLE job (
    id INT PRIMARY KEY IDENTITY (1,1),
    job_num VARCHAR(32) NOT NULL,
    mailing_id INT NOT NULL,
    personal_code NVARCHAR(50) NOT NULL,
    fname NVARCHAR(50) NOT NULL,
    lname NVARCHAR(50) NOT NULL,
    email NVARCHAR(50),
    address NVARCHAR(50),
    city NVARCHAR(50),
    state CHAR(2),
    zip NVARCHAR(10),
    extra NVARCHAR(150)
);

CREATE TABLE customer (
    id INT PRIMARY KEY IDENTITY (1,1),
    fname NVARCHAR(50) NOT NULL,
    lname NVARCHAR(50) NOT NULL,
    email NVARCHAR(50),
    address NVARCHAR(50),
    city NVARCHAR(50),
    state CHAR(2),
    zip NVARCHAR(10)
);

CREATE TABLE personal_code (
    id INT PRIMARY KEY IDENTITY (1,1),
    customer_id INT NOT NULL,
    mailing_id INT NOT NULL,
    personal_code NVARCHAR(50) NOT NULL,
    email NVARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customer(id)
);

CREATE TABLE personal_code_extra (
    personal_code_id INT PRIMARY KEY,
    extra NVARCHAR(150),
    FOREIGN KEY (personal_code_id) REFERENCES personal_code(id)
);

作业表由一个外部进程填充，该进程参与创建将向其发送邮件的地址列表。然后它调用我希望优化的 SP，传递 job_num。然后，SP 从具有该 job_num 的作业表中读取所有记录，并将存储在那里的数据插入到其他三个表中。

正如预期的那样，客户表是存储各种邮件收件人数据的地方。

personal_code 表存储与邮件相关的个人代码数据。每个代码都与特定的邮件以及特定的客户相关联。目前，SP 为作业表打开一个游标。然后它遍历每一行，并为每一行执行以下操作：

1. 如果 address 不为 null，则将 @customer_id 设置为 customer.id，其中 fname、lname 和 address 匹配；否则，它将其设置为等于 customer.id，其中 fname、lname 和电子邮件匹配并且地址为空或空。
2. 此时，如果@customer_id 为空，则没有匹配的记录，将向客户添加一条新记录。@customer_id 设置为 scope_identity()。
3. 一条记录被插入到personal_code 中。
4. 如果 job.extra 不为空，则将一条记录插入到 personal_code_extra 中，（当然）使用第 3 步中插入生成的 id。

customer 和 personal_code 是数据库中最大的表，分别有 4000 万和 6400 万条记录。即使对客户的查询具有覆盖索引，对工作中的每一行进行单独搜索也必须减慢速度。我非常想放弃光标并用基于集合的方法替换这种 RBAR 方法。阻止我这样做的原因是不得不为personal_code 插入使用新创建的customer_id，以及为“extras”表使用新创建的personal_code_id。如果不是这样，我可以做类似的事情

INSERT INTO personal_code (fields)
SELECT (fields)
FROM job j
INNER JOIN customer c ON j.fname = c.fname AND j.lname = c.lname AND j.address = c.address
WHERE j.address IS NOT NULL;

INSERT INTO personal_code (fields)
SELECT (fields)
FROM job j
INNER JOIN customer c ON j.fname = c.fname AND j.lname = c.lname AND j.email = c.email
WHERE j.address IS NULL;

INSERT INTO personal_code (fields)
SELECT (fields) -- but won't have a value for customer_id !
FROM job j
LEFT JOIN customer c ON either_address_or_email
WHERE c.id IS NULL;

以及如何以基于集合的方法处理“额外”内容的插入，我目前不知道。提前感谢您的任何想法。

编辑：每个请求添加的光标代码。这是简化的，但具有所有必需品——希望我的编辑是准确的。

DECLARE job_cur CURSOR FOR
SELECT mailing_id, personal_code, email, fname, lname, address, city, state, zip, extra
FROM job
WHERE job_num = @job_no;

OPEN job_cur;

FETCH NEXT FROM job_cur INTO @mailing_id, @personal_code, @email, @fname, @lname, @address, @city, @state, @zip, @extra;

WHILE @@FETCH_STATUS = 0
BEGIN

    IF ISNULL(@address, '') != ''
    SET @customer_id = (
        SELECT id 
        FROM customer 
        WHERE fname = @fname
        AND lname = @lname
        AND address = @address
    );
    ELSE
    SET @customer_id = (
        SELECT id 
        FROM customer 
        WHERE fname = @fname
        AND lname = @lname
        AND email = @email
        AND (address IS NULL OR address = '')
    ); 

    IF @customer_id IS NULL
    BEGIN

        INSERT INTO customer (
            fname, lname, address, city, state, zip, email
        )
        VALUES (
            @fname, @lname, @address, @city, @state, @zip, @email
        );

        SET @customer_id = SCOPE_IDENTITY();

    END

    INSERT INTO personal_code (
        customer_id, mailing_id, personal_code, email
    )
    VALUES (
        @customer_id, @mailing_id, @personal_code, @email
    );
    SET @personal_code_id = SCOPE_IDENTITY();

    IF @extra IS NOT NULL           
        INSERT INTO personal_code_extra (
            personal_code_id, extra
        )
        VALUES (
            @personal_code_id, @extra
        );

    FETCH NEXT FROM job_cur INTO @mailing_id, @personal_code, @email, @fname, @lname, @address, @city, @state, @zip, @extra;

END

我们正在运行 SQL Server 2008。在查看我在 sys.dm_db_missing_index_* 视图上运行的查询的结果时，我看到了一个关于包含列的奇怪建议。我有以下两个表：

create table foo (
    id int not null identity(1,1) primary key
    /* many more fields, not relevant to question */
)

create table bar (
    id int not null identity(1,1) primary key
    foo_id int not null,
    param_name nvarchar(50),
    param_value nvarchar(255)
)

foo 表与 bar 是 1:M 的关系；bar 用于存储与 foo 中的某些记录相关的杂项。

回到 sys.dm_db_missing_index_* 查询。一天中有几次，优化器会在 equality_columns 中使用 foo_id 并在 included_columns 中使用 id 查找 bar 上的索引。我的问题是，将表的聚集索引键包含在非聚集索引中有什么意义？既然非聚集索引的叶级无论如何都包括聚集索引的键值，那么在非聚集索引中包括聚集索引键不是多余的吗？

编辑： bar 上唯一存在的索引是以 PK 为键的聚簇索引。

提前致谢。

我正在编写一个将数据库名称作为参数并返回该数据库索引及其碎片级别的表的存储过程。这个存储过程将存在于我们的 DBA 数据库中（包含 DBA 用于监视和优化事物的表的数据库）。有问题的系统都是 SQL Server 2008 R2，如果这有所不同的话。

我已经完成了基本查询，但一直在尝试提供索引的实际名称。据我所知，该信息包含在每个人的 sys.indexes 视图中。我的具体问题是尝试从另一个数据库的存储过程中以编程方式引用该视图。

为了说明，这是有问题的查询部分：

FROM sys.dm_db_index_physical_stats(@db_id,NULL,NULL,NULL,NULL) p
INNER JOIN sys.indexes b ON p.[object_id] = b.[object_id] 
    AND p.index_id = b.index_id 
    AND b.index_id != 0

从@db_id 标识的数据库执行查询时，查询工作正常，因为它使用了正确的 sys.indexes 视图。但是，如果我尝试从 DBA 数据库中调用它，则它全部为空，因为 sys.indexes 视图用于错误的数据库。

更一般地说，我需要能够做这样的事情：

DECLARE @db_name NVARCHAR(255) = 'my_database';
SELECT * FROM @db_name + '.sys.indexes';

或者

USE @db_name;

我曾尝试使用字符串连接和 OBJECT_NAME/OBJECT_ID/DB_ID 函数的组合来切换数据库或引用其他数据库，但似乎没有任何效果。我很感激社区可能有的任何想法，但我怀疑我将不得不重新设计这个存储过程以驻留在每个单独的数据库中。

在此先感谢您的任何建议。