January 18, 2006 at 1:13 am
I need to add an indexed view on an existing table. Unfortunatly the table has been created with SET ANSI_NULLS = OFF. Inorder to be able to add the indexed view on that table I need to change the ANSI_NULLS setting of the table object to ON.
How can I do this? ( besides drop and create table & repopulate table!)
Thkx, Bart
January 18, 2006 at 3:50 am
I don't believe there's any other way than to drop and recreate.
You can check current settings by select objectproperty(object_id('dbo.myTable'), 'IsAnsiNullsOn')
In the notes on the 'IsAnsiNullsOn' property, BOL says:
IsAnsiNullsOn Scalar and Inline Table-valued Function, Procedure, Table, Trigger, View Specifies that the ANSI NULLS option setting for the table is ON,
meaning all comparisons against a null value evaluate to UNKNOWN.
This setting applies to all expressions in the table definition, including computed columns and constraints, for as long as the table exists.
1 = ON
0 = OFF
...which seems to indicate that it's a non-changeable setting.
I haven't been able to find any syntax in ALTER TABLE / COLUMN either that seems to affect the SET ANSI settings...
/Kenneth
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply