Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 153
Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 153 Other than setting the specific set of options, nothing more needs to be done for the optimizer to utilize an index with a query on a view. Essentially, the SQL SERVER Optimizer handles the view query in the same manner that it would a standard query against a table. The view cannot reference another view; only underlying tables are permitted and you must create the view with the SCEMABINDING option. Only the Enterprise and Developer editions support the creation of an indexed view. There are limitations to the content of the SELECT statement for the view definition. They are as follows: No use of *. A column name used as a simple expression cannot be specified in more than one view column. No derived tables. Rowset functions are not permitted. UNION, Outer Joins, Subqueries, or Self-joins cannot be used only simple Joins. No TOP, ORDER BY, COMPUTE, or COMPUTE BY clause. DISTINCT is not permitted. COUNT(*) cannot be used, but COUNT_BIG(*) is allowed. Aggregate functions: AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP are not permitted. A SUM function cannot reference a nullable expression. No use of full-text predicates CONTAINS or FREETEXT. Partitioned Views A partitioned view enables the creation of a view that spans a number of physical machines. These views can fall into one of two categories: local and distributed. A distinction is also made between views that are updateable and those that are read-only. The use of partitioned views can aid in the implementation of federated database servers, which are multiple machines set up to share the processing load. For more information on federated server implementations, see SQL Server blogs Online, Designing Federated Database Servers. TIPEXAM Know Your Options A lot of specific options need to be in place to allow for Indexed Views. Make sure you are confident with the set of configuration features that are needed. Make sure that you read up on this topic as presented in Chapter 7, Working with Views.
For high quality website hosting services please check tomcat web hosting website.