使用QUOTED_IDENTIFIER来解决“ALTER INDEX failed”错误

Question

就像以前的其他人一样，在我的SQL日志中，我看到一个定时作业由于错误1934而未能执行。

ALTER INDEX失败，因为以下SET选项具有不正确的设置：“QUOTED_IDENTIFIER”

阅读了这里, 这里和这里之后，我仍然不确定我下面的hack是否会根据其他地方给出的建议引入风险。我的经验不足是这个问题的主要原因。

原始代码如下:

DECLARE @Database varchar(255);
DECLARE @Table varchar(255);
DECLARE @cmd nvarchar(500);
DECLARE @fillfactor int = 90;

DECLARE DatabaseCursor CURSOR FOR
SELECT
    name
FROM
    MASTER.dbo.sysdatabases
WHERE
    name IN ('MyDbName')
ORDER BY
    name;

OPEN DatabaseCursor;
FETCH NEXT FROM DatabaseCursor INTO @Database;
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
      table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES
      WHERE table_type = ''BASE TABLE''';

        -- create table cursor  
        EXEC (@cmd);
        OPEN TableCursor;
        FETCH NEXT FROM TableCursor INTO @Table;
        WHILE (@@FETCH_STATUS = 0)
            BEGIN
                SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(varchar(3), @fillfactor) + ')';

                EXEC (@cmd);
                FETCH NEXT FROM TableCursor INTO @Table;
            END

        CLOSE TableCursor;
        DEALLOCATE TableCursor;
        FETCH NEXT FROM DatabaseCursor INTO @Database;
    END
CLOSE DatabaseCursor;
DEALLOCATE DatabaseCursor;

USE MyDbName;
GO
EXEC sys.sp_updatestats;

我相信这个错误是在一个特定表格的 XML 列中添加索引后开始出现的，但我并不100％确定。其他地方我们也有使用 XPATH 的存储过程。我知道其他人也遇到了同样的错误，但没有手动设置 QUOTED_IDENTIFIER 的经验，所以我正在寻求建议以及改进我的解决方法。

根据我所读到的，这个修订后的代码可以正常执行：

 SET @cmd = 'SET QUOTED_IDENTIFIER ON; ' + 
            'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(varchar(3), @fillfactor) + ')';

我的担忧是：

1. 这个更改是否引入了任何风险或其他危险，而这些危险不是立即显现的（仅仅因为它没有出错并不能让我放心！）
2. 如果没有，是否有一种方法可以确定确切地导致这个问题的原因（例如特定的索引或数据库列/过程）？
3. 如果有，是否可以在动态SQL中考虑到这一点，并更优雅地处理它？

感谢您提前分享您的专业知识。

- EvilDr

Answer 1

1. 不，没有风险。
2. 使用 TRY / CATCH 块捕获错误，这样你就可以在某个地方记录变量的内容。
3. 你可以在服务器属性中更改默认的连接设置，以便始终启用 QUOTED_IDENTIFIER。然而，这并不能完全保证，因为数据访问驱动程序可以覆盖连接设置（不同的驱动程序 - ODBC、OLE DB、本机客户端等 - 有稍微不同的默认值）。

- Roger Wolf

Answer 2

请将quoted_identifier选项设置为ON，然后再次测试。

SET QUOTED_IDENTIFIER ON

- Eralper

---

我应该在哪里做这个，在代码块的顶部吗？它已经设置在我的修复动态代码中，而且没有问题地运行。 - EvilDr

@EvilDr，您可以将SET语句作为脚本中的第一条语句运行，而不是在每个动态语句中运行。 - Dan Guzman

是的，将其用作第一条语句是安全的。 - Eralper

在动态语句中，您还可以使用QUOTENAME()函数来处理表名等。 - Eralper

这是绝对正确的，我也曾经遇到过存储过程的问题。 - krtek