Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
This is possible as a metadata only change. See Changing ANSI_NULLS setting on a table – Martin Smith Jun 20 '12 at 16:10

1 Answer

up vote 4 down vote accepted

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

The setting is captured at CREATE time.

share|improve this answer
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 – Thomas Stringer 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
Ah, good point. Thanks for the pointer! – Thomas Stringer Apr 3 '12 at 12:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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