Changing ANSI_NULLS setting on a table

Question

Can we use the following query to update ANSI_NULLS option.

sp_configure 'allow updates', 1

reconfigure with override

update sysobjects set status = status | 0x20000000 where name = 'tblClient'

I am not sure what it is doing. I got this after searching for a solution. Our db is SQL server 2008.

But while i am running this i got an error like this "Ad hoc updates to system catalogs are not allowed."

Please give your suggestions

Answer 1

The only way of updating system tables directly is to start the instance in single user mode and connect via the DAC. This is unsupported and unsafe however (the structure of them has changed since 2000 so the code you have won't work against them).

Luckily you don't need to do this though. You can use ALTER TABLE ... SWITCH to do this as a metadata only change in a safe way.

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