December 20, 2004 at 7:32 am
I am trying to index a view for performance reasons and get the message:
Cannot create index. Object 'My_TableName' was created with the following SET options off: 'ANSI_NULLS.'.
Does anyone know how to change this option for a table so the view can be created?
Thanks
MW
December 20, 2004 at 8:18 am
Inexed views are not straightforward. Read this article on how to. Helped me out a couple of weeks back.
Beware the pitfalls associated with them as I also found out.
http://www.sqlteam.com/item.asp?ItemID=1015
December 21, 2004 at 4:20 am
Have a look in BOL under Indexed Views ... theres a pretty big section on it! In my opinion though, they really aren't worth the effort and i'd devise another strategy if i were u (a set of smaller views!?). Apart from all the SET options that need to be correct, the amount of things you cannot do (if u ever get it to work!?) makes them enormously un-useful anyway!?
December 21, 2004 at 2:12 pm
Contrary to the above I have been able to replace a lot of trigger based Sumarization with indexed views successfuly and the report performance, locks minimization and management were all improved. It is true is not a silver bullet and that there are many restrictions but if you can use them you will not regreted! Just make sure you fullfill all restrictions and you will be impressed by the speed gains obtained through index materialization!
HTH
* Noel
December 21, 2004 at 2:24 pm
Did you have any issues where a table included in the view was created with Ansi Nulls off? How did you resolve this?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply