I have a table that mistakenly has ANSI_NULLS option set to OFF. I'd like to set it ON for this table. The table has millions rows of data. Is it possible to change this option without creating a new table with ANSI_NULLS ON, moving all the data into this new table and renaming this table. Currently downtime for using this solution makes it impractical. Is there another way?


As far as my quick and dirty tests show (using ALTER TABLE and ALTER INDEX), no

The setting is captured at CREATE time.

  • 1
    Isn't ANSI_NULLS a connection-specific setting? In other words, even with a table that is created with ANSI_NULLS OFF, if the connection has ANSI_NULLS ON it will regard SELECT statements with ANSI_NULLS ON Apr 3 '12 at 11:45
  • @Shark: ANSI_NULLs is connection specific, but the setting at CREATE time is stored. This matters for computed columns, indexed views etc
    – gbn
    Apr 3 '12 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.