sql-server - 无法通过 EF ExecuteSqlCommand 重建索引

Question

我有以下脚本来重建索引:

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 80

DECLARE TableCursor CURSOR FOR
    SELECT OBJECT_SCHEMA_NAME([object_id])+'.['+name +']' AS TableName
    FROM sys.tables

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
    EXEC (@sql)

    FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

我还有其他脚本以与此脚本相同的方式运行。

当我按以下方式执行此操作时:

var sql = ResourceUtilities.ReadResourceContent("rebuild_indexes.sql");
db.Database.ExecuteSqlCommand(sql);

我收到以下错误:

Incorrect syntax near 'TableCursor'.

ReadResourceContent 的实现细节无关紧要 - 我正在用它运行其他任意 SQL 并且它工作正常。

为什么会发生这种情况以及需要改变什么？

Answer 1

您至少应该尝试用分号终止所有行。虽然很少需要(据我所知，只有两个实例是 THROW 语句之前的语句，这些语句是在 SQL Server 2012 中引入的，并且是在 CTE 之前的)，但从发布时起，它已被官方视为最佳实践SQL Server 2005。

使用分号终止语句/查询的好处之一是，当出现行结尾不一致等问题时，SQL Server 将更轻松地解析查询批处理，这可能是此处问题的根本原因。我猜测根本原因是行结尾不一致，因为您可以通过 SSMS 针对 Azure 数据库运行脚本。如果 Azure SQL 数据库需要分号，那么即使通过 SSMS 运行也会产生错误。最有可能的是，SSMS 在提交批处理之前使行结尾保持一致，而通过 .NET 代码运行的操作不会自动为您完成。

其他说明:

• 最好不要混合使用 VARCHAR 和 NVARCHAR(尽管数据类型优先级会将其全部转换为 NVARCHAR)结尾) 。由于您正在处理标识符(即表名，在数据库中是 sysname 类型，它是 NVARCHAR(128) 的别名)，理想情况下全部应该是 NVARCHAR 以及所有以 N 为前缀的字符串文字。

• 在大多数情况下，特别是对于具有 Identity 列的表，FILLFACTOR 为 80 是很糟糕的，您应该使用 100。当使用 NEWID() 时> 然后从 90 开始，仅在必要时降低。对于 NEWSEQUENTIALID() 使用 100。

• 声明游标时，如果查询引用真实表而不是临时表，则使用 STATIC 关键字以免锁定基表。 通常使用以下关键字也不是一个坏主意:LOCAL READ_ONLY FORWARD_ONLY。

最终结果应如下所示:

DECLARE @TableName sysname, -- system alias for NVARCHAR(128)
        @SQL NVARCHAR(500),
        @FillFactor TINYINT; -- value cannot be < 0 or > 100 anyway

SET @FillFactor = 100; -- or 90 if using NEWID() for Clustered Index

DECLARE TableCursor CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY
FOR
    SELECT OBJECT_SCHEMA_NAME(st.[object_id]) + N'.' + QUOTENAME(st.[name]) AS [TableName]
    FROM   sys.tables st;

OPEN TableCursor;

FETCH NEXT
FROM  TableCursor
INTO  @TableName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @SQL = N'ALTER INDEX ALL ON '
               + @TableName
               + N' REBUILD WITH (FILLFACTOR = '
               + CONVERT(NVARCHAR(3), @FillFactor)
               + N')';

    EXEC (@SQL);

    FETCH NEXT
    FROM  TableCursor
    INTO  @TableName;
END;

CLOSE TableCursor;
DEALLOCATE TableCursor;