locked
Changing ANSI_NULLS setting on a table RRS feed

  • Question

  • I inherited a large fact table that was created with SET ANSI_NULLS OFF.  I would like to change the setting on the table without recreating it.  I am having trouble creating an indexed view on this table due to the ANSI_NULLS OFF setting that was used when this table was originally created.  Is there a way to change the ANSI_NULLS setting on this table without dropping and recreating it? 

    I am using SQL Server 2008 Enterprise with SP1.

    Thanks.

    Friday, August 26, 2011 3:04 PM

Answers

  • There is definitely not any supported way to do this.

    There may be an unsupported way to do this. That would require starting SQL Server in single-user mode, so that you access the real system tables directly and flip the status bit, but I am not really sure you can do this in SQL 2005 and later.

    We had this problem in our system, and at most sites we rebuilt the databases, but some customers didn't want to do that, so we simplyu update the system tables. In SQL 2000, before the upgrade to SQL 2005.

    And, no, it is not entirely without risk. The bit controls how CHECK constraints are implemented. There could be data that are accepted with ANSI_NULLS OFF, but which are invalid when ANSI_NULLS is ON.

    So rebuilding the table is definitely safer, even if takes longer time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Fred_1000 Wednesday, August 31, 2011 1:45 PM
    Friday, August 26, 2011 10:17 PM

All replies

  • ansi_nulls is a connection setting (and also has a <default> setting at the database and user level).  You should consult with your DBA about turning this setting on since it generally is on by default (indicating that someone turned it off intentionally). 

    You can override this setting by generating (or writing) a script to create the indexed view and set the appropriate connection settings on/off when you execute the script.  In SSMS, the default setting for the database can be changed by displaying the properties window for the database - options page.

    Friday, August 26, 2011 3:58 PM
  • Thanks for your reply.

    The issue is not with the indexed view.  The issue is that the table was created with this setting.  I need to change the ANSI_NULLS setting on the table, not the indexed view.  Do you know if there is a way to change the ANSI_NULLS setting on the table without dropping and recreating the table?  I also do not what to change the default for this setting across the whole database.

    Thanks.



    Friday, August 26, 2011 4:56 PM
  • Fred,

    Try to do a simple Alter on the table and see what happens.  For example try something like:

    BEGIN TRANSACTION
    
    
     GO 
    
    
    ALTER TABLE dbo.MyTable SET (LOCK_ESCALATION = TABLE)
    
    
     GO
    
    
     COMMIT
    
    


    What's supposed to happen, if SQL Server finds a table with ansi nulls set to OFF it's supposed to change it to ON.  A simple ALTER (one which doesn't re-create the table) might force that to happen without having to do a rebuild.

    However, it may be that SQL Server can't change the ANSI_NULL setting without doing a full rebuild in which case you don't have any options but to let it rebuild. 


    Tom Overton
    • Marked as answer by Fred_1000 Friday, August 26, 2011 6:16 PM
    • Unmarked as answer by Fred_1000 Wednesday, August 31, 2011 1:45 PM
    Friday, August 26, 2011 5:27 PM
  • Tom,

    I gave it a shot and the ALTER trick didn't work for me.  I am recreating the table.  Fortunately the person who set up the table used ANSI_NULLS ON in production.  I am rebuiling the table in my QA environment now.

    Thanks for responding.  I searched the internet and couldn't find a way to just change the setting on the table w/o dropping and rebuilding it.  Your trick was worth a shot.

    Thanks.

    Friday, August 26, 2011 6:19 PM
  • There is definitely not any supported way to do this.

    There may be an unsupported way to do this. That would require starting SQL Server in single-user mode, so that you access the real system tables directly and flip the status bit, but I am not really sure you can do this in SQL 2005 and later.

    We had this problem in our system, and at most sites we rebuilt the databases, but some customers didn't want to do that, so we simplyu update the system tables. In SQL 2000, before the upgrade to SQL 2005.

    And, no, it is not entirely without risk. The bit controls how CHECK constraints are implemented. There could be data that are accepted with ANSI_NULLS OFF, but which are invalid when ANSI_NULLS is ON.

    So rebuilding the table is definitely safer, even if takes longer time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Fred_1000 Wednesday, August 31, 2011 1:45 PM
    Friday, August 26, 2011 10:17 PM
  • Thanks.
    Wednesday, August 31, 2011 1:46 PM
  • I don't understand the question. As I understand it, the ANSI_NULL setting applies to the connection itself and I don't think it is stored with the table definition.
    Wednesday, August 31, 2011 5:09 PM
  • LadysMan,

    You're right, the ANSI_NULLS setting is at the database or session level.  However in some older versions of SQL Server (like 2000 and earlier I think) you could create a table and it could set ANSI_NULLS OFF at the table level.  When you bring a database over to SQL 2008 it will tell you that it's going to rebuild the table to ANSI_NULLS ON if it encounters one during a DDL operation. 


    Tom Overton

    Wednesday, August 31, 2011 5:20 PM
  • You can use

    ALTER TABLE ... SWITCH

    to do this as a metadata only change without touching the data pages at all.

    Create a new table with the same structure and desired ANSI_NULL setting then switch the old into the new, drop the old and rename the fixed table. 

    See here for example code.

    • Proposed as answer by Martin Smith Wednesday, June 20, 2012 4:13 PM
    • Edited by Martin Smith Wednesday, June 20, 2012 4:22 PM more details
    Monday, April 9, 2012 10:02 AM