April 13, 2013 at 4:46 pm
Hi,
DTA keeps recommending creating views and then building indexes on them. On a few high cost queries, the recommendation will be a 99% performance gain so I really want to do it but in the past I have done it twice and both times it caused an incident when it was time to do inserts on the table with an indexed view. This error has put me in a lot of hot water so I am afraid to make it again.
"INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations."
Are indexed views as dangerous as they seem?
Thanks for reading.
Howard
April 13, 2013 at 4:50 pm
Done right they are very useful. I have used them in the past and don't remember have the issues you indicated.
All I can say is test it in a development environment and see if you have those issues. If you do your choices are to 1) figure out why it is occurring and see if you can correct it, or 2) don't implement them in production.
April 13, 2013 at 6:15 pm
There are certain conditions and requirements to create and use indexed views; each of which can result in certain advantages and disadvantages....The following may not be an exhaustive list, refer to BOL...it has very detailed information on indexed views.
1. Since the view is materialized, any writes to underlying tables would be propagated to the view also..so your time of doing the writes has increased. So use this with caution if any of the underlying tables are involved in heavy writes.
2. The indexed view is always defined to be schemabound to underlying tables. Because of this, you cannot change the schema of underlying tables without removing the schema binding.
3. You cannot use other views or sub queries to define an indexed view.
4. Your creation of an indexed view is only complete when you actually create an index on the view. If you do not do this, but use SCHEMABINDING and other settings, your view is not yet an indexed view.
4. You have to create an unique clustered index. Non-clustered indexes can only be created after creating a UNIQUE Clustered Index. That means you need to come up with a set of column(s) that is unique across the whole result set. So sometimes your clustered index could be more wide than your liking.
5. Your view definition should be deterministic. Than means non-deterministic functions cannot be used in the definition. For example, if you have a column calculating the number of days between an order date and today, you cannot do it because GETDATE is non-deterministic.
6. You can use an imprecise datatype such as float in the view definition, but they cannot participate in either the index, GROUP BY clause, WHERE clause
7. And this is also where the various SET statements come into play also. For example, ANSI_NULLS will give you different values when compared against NULL. So there are certain SET statements that must be defined when you create the view.
8. You cannot use DISTINCT keyword (probably becuase of how DISTINCT treats NULL; others may clarify/correct)
9. You cannot use UNION, EXCEPT, INTERSECT. May be because UNION implies DISTINCT
10. You cannot use UNION ALL (not sure why though)
11. You cannot use OUTER JOINS
With the limitations, you would have to write a very good or very complicated select statement to satisfy all the needs.
They can be good when used correctly (in less writes-more reads situation) and provided you can write a query that satisfies the conditions.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply