sql - 更新现有表中的 ANSI_NULLS 选项

Question

在我们的数据库中有一个使用 ANSI_NULLS OFF 创建的表。 .现在我们已经使用这个表创建了一个 View 。我们想为这个 View 添加一个聚集索引。

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

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

有什么办法可以改变表来修改这个选项。请提出您的建议。

Answer 1

这是 cross posted on Database Administrators所以我也不妨从那里发布我的答案，以帮助 future 的搜索者。

它可以使用 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, [reseed value]) 再次正确设置种子值。