更新现有表中的ANSI_NULLS选项

Question

在我们的数据库中，有一个用创建的表ANSI_NULLS OFF。现在，我们使用此表创建了一个视图。我们想为此视图添加聚簇索引。

创建聚簇索引时，它会显示一个错误，例如无法创建索引，因为此特定表的ANSI_NULL已关闭。

该表包含大量数据。因此，我想将此选项更改为ON，而不会丢失任何数据。

有什么方法可以更改表来修改此选项。请提出您的建议。

Answer 1

这是交叉发布在数据库管理员上的，所以我也可以在这里发布我的答案，以帮助将来的搜索者。

可以使用仅更改元数据（即，无需将所有数据迁移到新表）进行更改ALTER TABLE ... SWITCH。

下面的示例代码

/*Create table with option off*/ 
SET ANSI_NULLS OFF;

CREATE TABLE dbo.YourTable (X INT)

/*Add some data*/ 
INSERT INTO dbo.YourTable VALUES (1),(2),(3)

/*Confirm the bit is set to 0*/ 
SELECT uses_ansi_nulls, * 
FROM   sys.tables 
WHERE  object_id = object_id('dbo.YourTable')

GO

BEGIN TRY 
    BEGIN TRANSACTION; 
    /*Create new table with identical structure but option on*/
    SET ANSI_NULLS ON; 
    CREATE TABLE dbo.YourTableNew (X INT)

    /*Metadata only switch*/
    ALTER TABLE dbo.YourTable  SWITCH TO dbo.YourTableNew;

    DROP TABLE dbo.YourTable;

    EXECUTE sp_rename N'dbo.YourTableNew', N'YourTable','OBJECT';

    /*Confirm the bit is set to 1*/ 
    SELECT uses_ansi_nulls, * 
    FROM   sys.tables 
    WHERE  object_id = object_id('dbo.YourTable')

    /*Data still there!*/ 
    SELECT * 
    FROM dbo.YourTable

    COMMIT TRANSACTION; 
END TRY

BEGIN CATCH 
    IF XACT_STATE() <> 0 
      ROLLBACK TRANSACTION;

    PRINT ERROR_MESSAGE(); 
END CATCH;

警告：当表包含IDENTITY列时，您需要重新设定IDENTITY值。SWITCH TO将重置身份列的种子，并且如果您对身份没有UNIQUE或PRIMARY
KEY约束（例如，在SQL 2014中使用CLUSTERED COLUMNSTORE索引时），您将不会立即注意到它。您需要使用DBCC
CHECKIDENT（“ dbo.YourTable”，RESEED，[重新设置的种子值]）来再次正确设置种子的值。