Share this Page URL

Chapter 7. Working with SQL Server Views > Optimizing the Views That You Build - Pg. 229

Working with SQL Server Views 229 When to Use Indexed Views Indexed views are not appropriate in all situations. Although indexed views speed up data retrieval, they slow data updates. You must therefore ascertain that the benefits of data retrieval performance outweigh the performance degradation experienced for data update operations. Requirements for Indexed Views Not all views can be indexed. A view must meet all of the following requirements for you to index it: · The view cannot reference other views. · The tables underlying the view must be in the same database as the view and must have the same owner as the view. · You must set the ANSI_NULLS option to ON when you create the tables referenced by the view. · You must set the ANSI_NULLS and QUOTED_IDENTIFIER options to ON before creating the view. · You must create the view and any functions underlying the view with the SCHEMABINDING option. This means that you cannot modify or drop tables and other objects underlying the view without dropping the view first. In addition to the limitations for the view, there are limitations for the syntax within the view. They are: · You cannot use * to designate all columns. · You cannot use the keyword UNION. · You cannot use the keyword DISTINCT.