Is there an easy way to switch a table from "ANSI NULLS OFF" to "ANSI NULLS OFF"

We have some tables with quite a lot of data (in the order of tens of
millions of rows) which were created with ANSI NULLS OFF. Now that we
want to create some indexed views we need these tables to have ANSI
NULLS ON.
We can create new temp tables, bulk copy the data over and recreate all
cosntraints and indices.
But is there an easier way?
Thanks,
AnilA table doesn't care about this setting. It is the connection that is working against this table
which need to have the correct setting.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sqlpractitioner@.gmail.com> wrote in message
news:1160615076.764380.293820@.i3g2000cwc.googlegroups.com...
> We have some tables with quite a lot of data (in the order of tens of
> millions of rows) which were created with ANSI NULLS OFF. Now that we
> want to create some indexed views we need these tables to have ANSI
> NULLS ON.
> We can create new temp tables, bulk copy the data over and recreate all
> cosntraints and indices.
> But is there an easier way?
> Thanks,
> Anil
>|||Tibor Karaszi wrote:
> A table doesn't care about this setting. It is the connection that is working against this table
> which need to have the correct setting.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
The setting at table-level does matter if you create computed columns
or if you need to create an indexed view.
Unfortunately, the only way I know of to change it is to recreate the
table.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> The setting at table-level does matter if you create computed columns
> or if you need to create an indexed view.
Indeed, I just tried with an index over a computed columns.
Thanks, David. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1160645954.605837.156410@.e3g2000cwe.googlegroups.com...
> Tibor Karaszi wrote:
>> A table doesn't care about this setting. It is the connection that is working against this table
>> which need to have the correct setting.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
> The setting at table-level does matter if you create computed columns
> or if you need to create an indexed view.
> Unfortunately, the only way I know of to change it is to recreate the
> table.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||> The setting at table-level does matter if you create computed columns
> or if you need to create an indexed view.
> Unfortunately, the only way I know of to change it is to recreate the
> table.
>
Indexed views is the reason we are looking at this. Do you know if
there is an easier option in SQL 2005? We are on SQL 2000 now.
Thanks,
Anil|||<sqlpractitioner@.gmail.com> wrote in message
news:1160670946.200564.266370@.m73g2000cwd.googlegroups.com...
>> The setting at table-level does matter if you create computed columns
>> or if you need to create an indexed view.
>> Unfortunately, the only way I know of to change it is to recreate the
>> table.
> Indexed views is the reason we are looking at this. Do you know if
> there is an easier option in SQL 2005? We are on SQL 2000 now.
> Thanks,
> Anil
>
There is no change in 2005 that I know of. ANSI NULLS ON has been the
preferred option for so long, maybe supporting the OFF setting isn't a high
priority for MS. If you want to change that you could post a suggestion at:
http://connect.microsoft.com/SQLServer/feedback/
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Oct 12, 5:35 pm, sqlpractitio...@.gmail.com wrote:
> > The setting at table-level does matter if you create computed columns
> > or if you need to create an indexed view.
> > Unfortunately, the only way I know of to change it is to recreate the
> > table.Indexed views is the reason we are looking at this. Do you know if
> there is an easier option in SQL 2005? We are on SQL 2000 now.
> Thanks,
> Anil
There is no change in 2005 that I know of. ANSI NULLS ON has been the
preferred option for so long, maybe supporting the OFF setting isn't a
high priority for MS. If you want to change that you could post a
suggestion at:
http://connect.microsoft.com/SQLServer/feedback/
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--