Update ANSI_NULLS option in an existing table

This was cross posted on Database Administrators so I might as well post my answer from there here too to help future searchers.

It can be done as a metadata only change (i.e. without migrating all the data to a new table) using ALTER TABLE ... SWITCH.

Example code below

/*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; 

WARNING: when your table contains an IDENTITY column you need to reseed the IDENTITY value. The SWITCH TO will reset the seed of the identity column and if you do not have a UNIQUE or PRIMARY KEY constraint on the identity (e.g. when using CLUSTERED COLUMNSTORE index in SQL 2014) you won't notice it right away.You need to use DBCC CHECKIDENT ('dbo.YourTable', RESEED, [reseed value]) to correctly set the seed value again.

Unfortunately, there is no way how to do it without recreating. You need to create new table with ANSI_NULLS ON and copy there all data.

It should be something like:

SET ANSI_NULLS ON;CREATE TABLE new_MyTBL (....)-- stop all processes changing your data at this pointSET IDENTITY_INSERT new_MyTBL ONINSERT new_MyTBL (...)   -- including IDENTITY field SELECT ...               -- including IDENTITY field FROM MyTBL SET IDENTITY_INSERT new_MyTBL OFF-- alter/drop WITH SCHEMABINDING objects at this pointEXEC sp_rename @objname = 'MyTBL', @newname = 'old_MyTBL'EXEC sp_rename @objname = 'new_MyTBL', @newname = 'MyTBL'-- alter/create WITH SCHEMABINDING objects at this point-- re-enable your processesDROP TABLE old_MyTBL      -- do that when you are sure that system works OK

If there are any depending objects, they will work with new table as soon as you rename it. But if some of them are WITH SCHEMABINDING you need to DROP and CREATE them manualy.

I tried the SWITCH option recommended above but was unable to RESEED the identity. I could not find out why.

I used the following alternative approach instead:

1. Create database snapshot for the database that contains the table
2. Script table definition of the table you intend to update
3. Delete the table that you intend to update (Make sure the database snapshot is successfully created)
4. Update SET ANSI NULLs from OFF to ON from the script obtained from step 2 and run updated script. Table is now recreated.
5. Populate data from database snapshot to your table:SET IDENTITY_INSERT TABLE_NAME ONINSERT INTO TABLE_NAME (PK, col1, etc.)SELECT PK, col1, etc.FROM [Database_Snapshot].dbo.TABLE_NAMESET IDENTITY_INSERT TABLE_NAME OFF
6. Migrate non clustered index manually (get script from database snapshot)

Using the above:

• I did not have to worry about constraints and keys since table/constraint names always remain the same (I do not need to rename anything)
• I have a backup of my data (the snapshot) which I can rely on to double check that nothing is missing.
• I do not need to reseed the identity

I realize deleting table may not always be straightforward if table is referenced in other tables. That was not the case for me in this instance.. I was lucky.