Database Management and Programing Article

  In this paper, SQL Server 2005 Enterprise Edition in view of improved function of the index.    In view of the index that was introduced and discussed by improving the performance of the functions of some of the specific circumstances 

  First, the index view 

  Over the years, Microsoft ® SQL Server ™ has consistently supported the view as to create a virtual table.    Typically, the primary role of these views is: 

•

  Provide a security mechanism, the user will be restricted to one or more of a table of data subset. 

•

  Provide a mechanism to allow developers to customize the user to see through the logic of the table stored in the data. 

  Through SQL Server 2000, SQL Server view of the function of the expansion of the system to achieve performance gains.    In view can be created only on the accumulation index and the index of non-gathering, to improve the most complex data access for the performance.    In SQL Server 2000 and 2005, is the only index of view is the accumulation index view.    This article discussed the content applies to SQL Server 2005, many of which also apply to the content of SQL Server 2000. 

  From the database management system (DBMS) point of view, view of the data (a type of meta-data) a description.    When the creation of a typical view, through a package SELECT statements (as defined in a result set to show a virtual table) to define metadata.    When another query in the FROM clause cited in view, the system will retrieve the metadata directory and alternative view of the expansion of the use of metadata.    After the expansion of view, SQL Server Query Optimizer will compile a query for the implementation of the Plan of Implementation.    Query Optimizer will search for a search query may be a group of the implementation plan and in accordance with the implementation plan for each query the actual time estimates, the choice could find the lowest cost plan. 

  For non-indexed view, for the necessary analytical view of the operation will be concrete.    Any calculation (for example: Log on or polymerization) are each cited in the view of the query to complete the implementation of a.    In view of the creation of a unique gathering index, the view of the result set was specific, and the preservation of the physical storage in the database, thus saving time in the implementation of the implementation of this high-cost operating costs. 

  In the implementation of the enquiries can be two ways to use the index view.    Enquiries can be directly invoked the index view, or, more importantly, if the query optimization with the view to determining the replacement cost of the lowest in the plan for some or all enquiries, you can select it.    In the second case, the use of the index and its view alternative forms the basis of the general index.    Do not have to view in the query used to make queries in the query optimization for the implementation of the use of this view.    This makes the existing application can re-create the index benefited from view, without having to make changes. 

  Note index view is SQL Server 2000 and 2005 versions of a function.    In SQL Server 2000 and 2005 and the Enterprise Developer version, the query processor can use the index to view the structural analysis of the view that matches the query, even if not quoted by name to view.    In other versions, to be quoted by name to view, view and apply for the use of NOEXPAND prompts to view the contents of the index. 

  To improve the performance of the index view 

  Use of the index increase query performance is not a new concept, but the index provided some view the index can not be achieved by the standard of performance gains.    Index may view the adoption of the following ways to enhance query performance: 

•

  Polymerization can be calculated in advance and kept in the index, the query implementation, minimize the cost calculations. 

•

  Can be connected in various forms in advance and save final data sets. 

•

  Retain connectivity or a combination of polymerization. 

  When the description of the plan for optimization of the use of index-view, usually can be obtained by the performance improvements.    Cited by the complexity of the query are different (for example: polymerization calculating the number of tables used by the quantity or the number of predicate) and includes production from the real environment with millions of lines of the table. 

  In view gathered on the use of non-index 

  Secondly, the view on the non-gathering Index will provide a better query performance.    And the list of non-indexed gathered similar view on the non-gathering Index will provide more options for the query optimization is in the process of compiling choice.    For example, if the query contains gathered by the index are not covered, then the plan can be optimized to select one or more auxiliary index, the index view or to avoid time-consuming form of a complete scan. 

  Add the structure of the database index will increase costs, because the index needs ongoing maintenance.    In the index number and maintenance costs of finding the right balance, should be carefully weighed. 

  Second, the index view of the merits of 

  In view of the implementation of the index, database analysis of the work load.    Enquiries and use various tools (such as: SQL Profiler) to determine the knowledge can benefit from the index view of the query.    Frequent polymerization and connectivity in the most appropriate use of the index view.    Whether or not frequent, as long as the need for a very long response time, fast response to the high overhead, then the index for the use of view.    For example, some developers found for senior executives in running the end of the report, a pre-computing and storage for the index of respondents view useful. 

  Not all enquiries can benefit from the index view.    With the general index of similar, if not using the index view, will not be able to benefit from it.    In this case, not only unable to achieve performance improvement, but also in disk space, maintenance and optimization generate additional costs.    However, when using the index view, it can greatly improve (on the order of magnitude) data access.    This is because the query optimization for the use of storage in the index view (for the implementation of a significant reduction of costs) calculated in advance the outcome. 

  Query Optimizer has only to consider the cost index for the use of view.    So as to avoid such a situation: the cost for optimizing the use of the index higher than that of view by saving the cost of trying to match various index view.    Cost less than 1 in the index for use in very good view. 

  Index view from the implementation of the benefits of applications including: 

•

  Decision Support work load 

•

  Data Market 

•

  Data Warehouse 

•

  On-line analytical processing (OLAP) storage and source 

•

  Data mining work load 

  Enquiries from the type and model in terms of benefit applications in general include: 

•

  Large sheet of connectivity and polymerization 

•

  For enquiries, the repeat mode 

•

  Several groups of the same or overlapping rows on the duplication of polymerization 

•

  The same button on the table to repeat the same connection 

•

  The combination of the above 

  On the contrary, many write-implementation of the on-line transaction processing (OLTP) systems, or frequent updates of the database application may not be able to use the index view, because at the same time view and update the bottom of the table will result in higher maintenance costs. 

  Query Optimizer how to use the index view 

  SQL Server query optimization to automatically determine when a given query on the implementation of the use of the index view.    Not directly quoted in the query view is optimized for the implementation plan for the use of.    Therefore, existing applications can use the index view, rather than change the application itself; only need to create the index view. 

  Optimization for consideration 

  Query Optimizer by considering several conditions to determine whether the index view for the whole or part.    These conditions counterparts for a FROM clause in the following several components: 

•

  Enquiries FROM clause in the table must be indexed view FROM clause in the form of super-sets. 

•

  For enquiries in connection conditions must be the view of the ultra-connected conditions. 

•

  Polymerization out in the query must be from the view of the polymerization out a subset of derivative. 

•

  Choice for all the expressions in the list view must be chosen from the list or not included in the definition of view derived from the table. 

•

  If the other by matching the predicate to the super-set match, then the predicate will be included in another predicate.    For example, "Ta = 10" classified "Ta = 10 and Tb = 20".    Predicate can be included in any of its own.    Table view restrictions on the part of the predicate must be included in the restrictions on the same table for that part of the predicate.    In addition, SQL Server must be verifiable way to achieve this. 

•

  A view of the definition of the table for search out all the conditions predicate must appear in the following view of the definition of one or more of: 

1.

  A GROUP BY list. 

2.

  Select the list view (if there is no GROUP BY). 

3.

  View the definition of the same or equivalent to the predicate. 

  (1) and (2) to allow SQL Server to view the list of predicate for, in order to further restrict the view out.    (3) is rather special.    In this case, no need to filter out, so the column does not need to appear in view. 

  If the query includes more than a FROM clause (a query, derived table, UNION), optimized for several possible options to deal with enquiries index view, and they are applied to different FROM clause.  2

  This document provided at the end of these cases involved the specific query.    Recommended best practices is to allow for optimization in the decision-implementation plan for the use of the index which (if any). 

  Use NOEXPAND view tips 

  When SQL Server handle quoted by name for the view, the view of the definition used only in-form only when the expansion will be normal.    This process is known as expansion of view.    Its expansion is a form of Acer. 

  NOEXPAND view tips can be mandatory for Optimizer will view the index as a gathering of ordinary table.    Prevent the expansion of its view.    Only in the FROM clause in the direct quote index view, the application will NOEXPAND tips.    For example, 

  SELECT Column1, Column2, ... FROM Table1, View1 WITH (NOEXPAND) WHERE ... 

  To ensure that SQL Server through their own view rather than read from the table-read data to deal with enquiries, then can use NOEXPAND.    If for some reason, SQL Server chosen a plan for the base table to deal with enquiries, and you want to use their view, then you might consider using NOEXPAND.    In addition to Developer, and Enterprise versions of the SQL Server used in all versions of SQL Server NOEXPAND to allow direct view of the index handle enquiries.    SQL Server can see the plan as a graphic expression of choice to use SQL Server Management Studio tool is expected to show the function of the Plan of Implementation of the phrase.    Or, we can see the use of SHOWPLAN_ALL, SHOWPLAN_TEXT or SHOWPLAN_XML the different non-graphic expression.    See SQL Sever Books Online SHOWPLAN in the different versions of the discussion. 

  Tips for using EXPAND VIEWS 

  Treatment quoted by name for the view, unless the view quoted add NOEXPAND tips, SQL Server or expansion of the Federation of view.    The tips will try to match the index view and expansion of enquiries, unless the query at the end of a OPTION clause in the designated EXPAND VIEWS for tips.    For example, if a database index view View1.    In the bottom of the query, according to its logical definition of (the CREATE VIEW statement) View1 were extended, and then EXPAND VIEWS options in the plan would prevent the use of the index View1 view to resolve the query. 

  SELECT Column1, Column2, ... FROM Table1, View1 WHERE ... 
  OPTION (EXPAND VIEWS) 

  To ensure that SQL Server queries through from the base quoted by the table to deal with direct access to data for the query, without having to visit the index view, it can be used EXPAND VIEWS.    In some cases, EXPAND view contribute to the elimination of the use of the index view and lead to the contention.    Application of the test procedures, NOEXPAND and EXPAND VIEWS can help users in the use and not use the index view of the performance evaluation conducted. 

  Third, SQL Server 2005 index view of the new features 

  SQL Server 2000 and compared, SQL Server 2005 contains many index view of the improvements.    Can be indexed site has been extended to include based on the following view: 

  • scalar polymerization, including SUM and without the GROUP BY COUNT_BIG. 
  • scalar expression and user-defined functions (UDFs).    For example, to set a table T (a int, b int, c int) and a scalar UDF dbo.MyUDF (@ x int), T view on the definition of the index calculation may include a list (for example: a + b or dbo. MyUDF (a)). 
  • imprecise permanent out.    Imprecise out a floating-or-out is, or is a derivative of floating or out of the calculation is shown.    In SQL Server 2000, if not a key part of the index, are imprecise index can be used to view the choice of the list.    Imprecise out can not be used to view the definition of other places (such as: WHERE or FROM clause).    If imprecise out in the permanent preservation of the table, then SQL Server 2005 to allow its accession to key or view definition.    Permanent column contains regular columns and marked as PERSISTED out of the calculation. 
  • imprecise are unable to join the non-permanent index or indexed view of the fundamental reasons are: the database must be divorced from the original computer, and then attached to another computer.    After the completion of the transfer, stored in the index or index view of all the values are calculated in the new hardware on the way to be derived from the old hardware with the same precision to each place.    Otherwise, these indexing view of the new hardware will be destroyed by logic.    As a result of this damage, in the new hardware, in response to the query view the index based on whether the use of the index table to view or derivative-based view of data, to return to a different response.    In addition, the new computer can not maintain a normal index view.    Unfortunately, the floating-point on different computer hardware (even if the same manufacturers in the same processor architecture) processor versions are not always identical.    For a certain floating-point value and b, may lead to a new firmware upgrade on the hardware (a * b) is different from the old hardware (a * b).    For example, the result may be very similar, but there are still nuances.    Before the index has been retained in the calculation of inaccurate addresses such separation / additional inconsistency, because in view of the index and the index update and maintain the database during the same computer has assessed all expressions. 
  • common language runtime (CLR) types.    SQL Server 2005 a major new feature is based on CLR support the user-defined types (UDT) and the UDF.    If the bar or expressions of uncertainty or permanent and precise, or combine the two, then will be out in CLR UDT or derived from these out from the expression on the definition of the index view.    In the index can not view the use of user-defined CLR polymerization. 

  Optimizer match query and view the index (so that it can plan for the use of) the function of the expansion include: 

  • a new type of expression in the SELECT query or view the list or conditions, involving: 

  • scalar expressions (such as (a + b) / 2). 
  • scalar polymerization. 
  • scalar UDF. 
  • interval classified.    Optimization can be detected index view of the definition of conditions covering intervals or "classified" in the query interval conditions.    For example, optimization can be identified "a> 10 and a <20" cover "a> 12 and a <18". 
  • expression equivalent.    Although certain expressions in the grammar are different, but in the end the result is the same, it can be regarded as equivalent.    For example, "a = b and c <> 10" and "10 <> c and b = a" equivalence. 

  Also, if there are a lot index in the database view, the contrast in their views on the definition of the compiler of the table, SQL Server 2005 is usually faster than a lot of SQL Server 2000.  .

  4, design matters to the attention of a database system to identify the appropriate index may be very complex.    If the general index in the design of the need to consider many possibilities, then add the index view of the structure will substantially increase the potential outcome of the design and complexity.    For example, the index can be used to view: 

  • cited in the table for any subset. 
  • the table for the subset of the conditions of any subset. 
  • portfolio out. 
  • aggregation function (for example: SUM). 

  Should also design table and index view on the index, from each structure in order to get the best results.    As the index and index view of a given query may be very useful, from the design of the proposal would lead to redundant, resulting in higher storage and maintenance expenses.    Optimization of the physical database design, it must weigh a different set of queries and database system must support the updated performance requirements.    Therefore, the index set an appropriate view of the physical design is a challenging task, should do everything possible to optimize the use of consultants database (Database Tuning Advisor). 

  If for the establishment of a special enquiry, query optimization is considering a number of index view, then query optimization costs will increase significantly.    Query Optimizer will be in the query of the table on the definition of any subset of all the index view.    In view rejected before, we must investigate every view for the replacement.    This may take some time, especially when given for existence of hundreds of such views when. 

  In its create a unique index gathered before, the view must meet several requirements.    At the design stage, consider these requirements: 

  • view and the view of all forms must be quoted in the same database and have the same owner. 
  • Index view do not have to contain the query used for the optimization of the use of the all-table. 
  • In any other view on the creation of the index prior to the creation of a unique gathering of the index. 
  • In-form to create, view and index, and table-and view the data has been changed, it should be properly set up some SET options (all in detail later in this document).    In addition, the SET options unless they are correct, otherwise query optimization does not consider the index view. 
  • to create a binding framework for the use of view, and also to be created through the SCHEMABINDING option cited in the view of any user-defined functions. 
  • require additional disk space to view the index kept by the definition of data. 

  Design guidelines 

  Design index view, consider these guidelines: 

•

  Design for several enquiries or more of the use of the index view. 

  For example, contains the SUM and COUNT_BIG out of view for the index include function SUM, COUNT, COUNT_BIG or for the use AVG.    For enquiries, the speed will be faster, because only a small number in view of the line retrieval without having to search all of the line in the table, and function required for the implementation of AVG part of calculation has been completed. 

•

  Key index to remain simple. 

  Through the key index in at least as far as possible the use of columns and bytes, the index of the view out to achieve more efficient access, because the index out of the more narrow view, the key comparison faster than the broader bond faster.    In addition, the index view on the definition of each non-accumulation index, the index keys have been gathered for the line locator.    The key index higher as the cost of gathering views on non-index in proportion to the amount of growth. 

•

  Considering the size of the final index view. 

  For pure polymerization, if the index view of the size of the original form of similar size, may not realize the tremendous performance improvements. 

•

  Design of a number of smaller local index view to speeding up the process. 

  May not be the total for the entire index for the design of a view.    As to how to do so, consider creating a number of index view, the implementation of the query. 

  Consider this a few examples: 

•

  Often the implementation of the queries in a database polymerization data, and in another polymerization data in the database and then connected the results.    Because the index can not view reference table from multiple databases, so users can not design a view to the implementation of the whole process.    However, in each database can create an index to view a database of various polymer operation.    If the match can be optimized and view the index for existing, then at least polymerization faster processing speed, not for the current re-coding.    Although the connection will not speed up the processing, but the query will change soon, because of its use of storage in the index in view of the polymerization. 

•

  Often for the implementation of the polymerization from several tables of data, then use the results UNION merger.    Index view does not support UNION.    Can design a number of polymerization view to the implementation of each operation.    Thereafter, the Optimizer may choose to view the index to speed up the enquiries without having to re-encoded query.    Despite the improvement UNION treatment, but to improve the various polymerization process. 

  There can help choose the tools you view the index » 

  Database optimization consultants (DTA3) is a SQL Server 2005 features to help administrators optimize the physical database design.    In addition to the proposed use of the index and table on the table and index of regional strategy, DTA has also recommended the use of the index view.    DTA can be used to strengthen administrators determine the index, view and zoning strategy (which can be optimized for the implementation of the database for the typical combination of performance) the ability of the portfolio.    DTA will be referring users to view a wide range of the index.    Including the use of SQL Server 2005 index view of the new features (in the "SQL Server 2005 index view of what's new» "one has described) index view.    DTA has not ruled out for database management in the design of physical storage structure of the need to make appropriate judgement.    However, it can be simplified physical database design process.    DTA recommended by a group of presumed index, view and district results, and based on the cost of the query optimizer to work together.    DTA is to optimize the use of estimates when not in use and the use of these structures at the cost of the work load, and recommend to offer a lower total cost of the structure. 

  Because the database optimization consultants to enforce all the necessary SET options (to ensure the accuracy of the results set), will successfully complete view of the creation of the index.    If, however, failed to be set-up options, the user applications may not be able to use these views.    If the request did not specify SET options, added to the index view of the definition of the implementation of the table insert, update or delete operation is likely to fail. 

  Updated data will be indexed view what changes » 

  Like any other index, when the data-sheet changes, SQL Server will automatically maintaining the index view.    The general index, each index are directly associated with a table.    As in the table on the basis of the implementation of each of the INSERT, UPDATE or DELETE operation, the index will be updated accordingly, thereby preserving the value in the index and the table is always consistent. 

  Index view has been the same maintenance; However, if the view cited a number of tables, then a table to update any need to update the index view.    Unlike the general index, in any form of participation in the insertion and his party may lead to the index occurred in view of the line changes.    This is because the lines may be inserted into another form of multiple lines of connection.    Update and delete rows of the same.    Therefore, the index view of the maintenance costs than maintaining the list of the index higher.    On the contrary, conditions for maintaining a highly selective view of the index than the cost of maintenance may be on the table the index is much lower, because most of the view quoted by the insertion of the table, delete and update the operation will not affect the view.    Do not have access to other databases for indexing data can be filtered view of these operations. 

  In SQL Server, you can update some of view.    When a view can be updated, will use INSERT, UPDATE and DELETE statement through the view directly modify the bottom of table.    In view of creating an index on the view will not stop the update.    The index is updated view of view will lead to under-table updates.    These updates are indexed view maintenance as part of automatic transmission back to the index view.    The view can be updated on the detailed information, see SQL Server 2005 for the SQL Server Books Online "in view through the modified data." 

  Attention maintenance costs 

  Design index view these points should consider the following: 

•

  Index view of the database needs additional storage.    Index view set of results in physics on the table with the typical storage similar reservations about the way in the database. 

•

  SQL Server will automatically maintain view, therefore, the view of the definition of the table of any changes that may trigger the index to view one or more changes.    So, will generate additional maintenance costs. 

  View received a net performance boost for its implementation to achieve the total cost savings for storage and maintenance costs and the margin. 

  Relatively easy to get close to view the required storage.    Through SQL Server Management Studio tool - is expected to show that the implementation plan, assessment of view definition package SELECT statement.    The tool will generate for the return of the number of lines and the approximate size firms.    Through the multiplication of these two values, the view may be closer to the size, but only approximate.    Only in the definition of view or the view of the implementation of enquiries on the creation of the index, the index to determine view on the actual size. 

  From the SQL Server automatically enforced by the maintenance of attention to matters of perspective, is expected to show that the implementation plan features may allow users to a certain extent, understand the impact of this spending.    If SQL Server Management Studio view of the amended assessment statement (on the view UPDATE, based in the table INSERT), the statement shows that the implementation plan will include the expression of the maintenance operation.    If the operation in a production environment to be carried out by the number of times to consider the cost, then the view may be maintenance costs. 

  The view is usually recommended as far as possible, or under the table-bulk (rather than separate) the implementation of any changes or updates operation.    This will reduce the view maintenance costs. 

  5, creating an index view 

  Index view to create the necessary steps for the successful implementation of critical view. 

1.

  For use in view of all the existing form, to confirm ANSI_NULLS the settings are correct. 

2.

  Create any new form, as shown in the table below confirmation of the current session has set up correctly ANSI_NULLS. 

3.

  Create any new form, as shown in the table below confirmation of the current session properly set up the ANSI_NULLS and QUOTED_IDENTIFIER. 

4.

  Confirmed with certainty definition of view. 

5.

  WITH SCHEMABINDING option to use a view. 

6.

  In view of the only gathered to create the index, confirmed the conversation SET options settings are correct, as shown below. 

7.

  In view of the gathering to create the only index. 

8.

  Available OBJECTPROPERTY function checks on existing table or view ANSI_NULLS and QUOTED_IDENTIFIER value. 

  SET option was the use of consistent results 

  If enquiries in the implementation of the current session of the opening of the SET different options, the same expression can be assessed in SQL Server 2005 produce different results.    For example, SET options CONCAT_NULL_YIELDS_NULL is set to ON, the expression 'abc' + NULL will return value NULL.    But when CONCAT_NULL_YIEDS_NULL is set to OFF, the same expression will generate 'abc'.    For the current session and quoted by the view of the target of several SET index options need to view the fixed value, to ensure the correct maintenance of view and return to consistent results. 

  As long as the following conditions exist, we must press the table in the "essential values" as shown in a conversation on the current value set SET options: 

•

  Created the index view. 

•

  Added to the index in view of the implementation of any form of any INSERT, UPDATE or DELETE operations. 

•

  Used for optimizing the index view generated query plan. 

  SET options necessary for the default value of the server OLE DB and ODBC value of DB LIB 

ANSI_NULLS

ON

OFF

ON

OFF

ANSI_PADDING

ON

ON

ON

OFF

ANSI_WARNINGS

ON

OFF

ON

OFF

CONCAT_NULL_YIELDS_NULL

ON

OFF

ON

OFF

NUMERIC_ROUNDABORT

OFF

OFF

OFF

OFF

QUOTED_IDENTIFIER

ON

OFF

ON

OFF

  ARITHABORT4 options must be set to ON, so that the current session to create the index view, but in SQL Server 2005, as long as the value of ANSI_WARNINGS ON, this option will automatically be set to ON, so no need to set them.    If you use OLE DB or ODBC server connection, simply modify ARITHABORT set value.    Sp_configure must use the server-level or use SET command from the application properly set up all DB LIB value.    SET options on the detailed information, see the SQL Server Books Online "in the use of options." 

  With the use of the function of uncertainty 

  The index must have a view of the definition of uncertainty.    If you choose to list all the expressions and WHERE GROUP BY clause and have a certainty, then the view on the certainty.    Certainty is always the expression of the adoption of a specific set of input to assess its value, to return to the same result.    The only certainty is uncertainty function will be to join the expressions.    For example, DATEADD function with certainty, because for any given a set of parameters, the function of its three parameters to return to the same result.    GETDATE do not have certainty, because it calls the same parameters, and its return to the values in each of implementation will change.    For more information, see SQL Server 2005 for the SQL Server Books Online "and is not a function of uncertainty." 

  Even with an expression of uncertainty (if it contains floating-point expression), according to the exact results may or micro-processor architecture code version of them.    In order to move between the computer database, SQL Server 2005 to ensure the integrity of data, such expressions can only view the index as a key non-listed to join.    Non-floating-point expression of the expression of certainty is considered accurate.    Only permanent and / or accurate expression of certainty to join the keys out of view and index WHERE or GROUP BY clause.    Permanent expression is saved out of use, including general PERSISTED out and marked out the calculation. 

  With COLUMNPROPERTY function and IsDeterministic view out whether the properties identified with certainty.    IsPrecise with COLUMNPROPERTY function and attributes identified with the SCHEMABINDING the view of the uncertainty out is accurate.    If the attribute to TRUE, COLUMNPROPERTY will return 1; such as FALSE, then back to 0, and, if for NULL, said invalid input.    For example, in this script 

  CREATE TABLE T (a int, b real, c as getdate (), d as a + b) 
  CREATE VIEW VT WITH SCHEMABINDING AS SELECT a, b, c, d FROM dbo.T 
  SELECT object_id ( 'VT'), COLUMNPROPERTY (object_id ( 'VT'), 'b', 'IsPrecise') 

  SELECT IsPrecise to return to 0, because it is classified as type b.    COLUMNPROPERTY can do some experiments, the T confirmed with certainty whether the other out and is accurate. 

  Other requirements 

  The index can view the collection it is possible to view a subset of the collection.    Any view can be indexed or not indexed in the circumstances could exist. 

  In addition to the design guidelines ( "SET option was to use the same results" and "the use of the function of uncertainty" in these two sections) listed in the request, but also must meet the following requirements in order to create a unique view on the accumulation index . 

  On-form requirements • 

  Quoted by the view of the table must be created in the table set when the option SET ANSI_NULLS the correct value.    OBJECTPROPERTY existing inspection function available on the table ANSI_NULLS value. 

  • the requirements of the relevant function 

  WITH SCHEMABINDING option must be used to create the view quoted by the user-defined functions. 

  • the request of the view 

  WITH SCHEMABINDING option must be used to create view. 

•

  Must be dual-use structure name (schemaname.tablename) quoted the view table. 

•

  Must be dual-use structure name (schemaname.functionname) quoted the view of a user-defined functions. 

•

  Options must be properly set up SET ANSI_NULLS and QUOTED_IDENTIFIER. 

  View restrictions 

  To SQL Server 2005 in view to create an index, the corresponding view definition must include: 

  ANY, NOT ANY 

  OPENROWSET, OPENQUERY, OPENDATASOURCE 

  Imprecise (floating-is-type) on the value of the arithmetic 

OPENXML

  COMPUTE, COMPUTE BY 

  ORDER BY 

  CONVERT generate a result of imprecise 

  OUTER Connection 

  COUNT (*) 

  Has been invoked with a banned gathering of the index-table 

  GROUP BY ALL 

  Use different data in the form or function 

  Derived from the table (FROM in the list of enquiries) 

  Another view quote 

DISTINCT

  ROWSET function 

  EXISTS, NOT EXISTS 

  Since the connection 

  Polymerization results (for example: SUM (x) + SUM (x)) on the expression 

  STDEV, STDEVP, VAR, VARP, AVG 

  By the predicate (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE) 

  Of enquiries 

  Imprecise constants (such as: 2.34 e5) 

  Can be empty of expression on the SUM 

  Embedded value function or table 

  Table tips (such as: NOLOCK) 

  MIN, MAX 

  text, ntext, image, filestream or XML out 

  Not the expression of uncertainty 

TOP

  Sort of non-unicode 

UNION

  SQL Server 2005 can be detected by the contradictions in that view will be empty (for example, when 0 = 1 and …) 

  Note index view may contain floating-and-out real However, such as non-permanent if the calculation out, it can not be included in the key index in the gathering. 

  GROUP BY restrictions 

  If there is GROUP BY, VIEW defined as: 

•

  Must contain COUNT_BIG (*). 

•

  Must not contain HAVING, CUBE, ROLLUP or GROUPING (). 

  These restrictions apply only to view the index definition.    Even GROUP BY not meet the above restrictions, in its enquiries can also be used in the Plan of Implementation of the index view. 

  The requirements of the relevant index • 

  CREATE INDEX statement the implementation of the user must view is the owner. 

•

  If the view definition includes GROUP BY clause, the only gathering of key index GROUP BY clause can only be invoked by the designated out. 

•

  Must not be opened IGNORE_DUP_KEY options in the case of creating an index. 

  6, sample 

  This section of examples on how to combine the two main index for the use of view: polymerization and connectivity.    At the same time, that the query optimizer in determining whether a particular view of the index used by the application of the conditions.    For complete list of conditions, see "query optimization for the use of the index view." 

  Based on these queries AdventureWorks in the table.    SQL Server 2005 AdventureWorks is a sample provided by the database, and write as a way to implement.    Before and after the creation of view, the user may want to use SQL Server Management Studio is expected to show the implementation of planning tools, to view the query optimization for the chosen plan.    Although these examples illustrate the optimization of low-cost option for the Plan of Implementation of the way, but AdventureWorks sample too small and can not be displayed to enhance the performance. 

  At the beginning of use of these examples, by running the following command to ensure that the conversation set the correct options: 

  Set 

  SET ANSI_NULLS ON 
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON

下列查询显示了两种方法用于从Sales.SalesOrderDetail 表返回具有最大总折扣的五个产品。

查询1

SELECT TOP 5 ProductID, Sum(UnitPrice*OrderQty) -
Sum(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rebate
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY Rebate DESC

查询2

SELECT TOP 5 ProductID,
SUM(UnitPrice*OrderQty*UnitPriceDiscount) AS Rebate
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY Rebate DESC

查询优化器所选的执行计划包含：

•

 ä¸€ä¸ªèšé›†ç´¢å¼•æ‰«æï¼Œä½äºŽä¼°è®¡è¡Œæ•°ä¸º121317 çš„Sales.SalesOrderDetail 表上。 

•

一个哈希匹配/聚合操作符，用于将所选的行放入基于GROUP BY 列的哈希表，并计算每行的SUM 聚合。

•

一个TOP 5 分类操作符，基于ORDER BY 子句。

视图1

添加包含Rebate 列所需聚合的索引视图将更改“查询1”的查询执行计划。在大型表（含数百万行）上，查询的性能也会得到大幅提升。

CREATE VIEW Vdiscount1 WITH SCHEMABINDING AS
SELECT SUM(UnitPrice*OrderQty) AS SumPrice,
SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS SumDiscountPrice,
COUNT_BIG(*) AS Count, ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID
  GO 
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)

第一个查询的执行计划显示Vdiscount1 视图被优化器所用。然而，该视图将不被第二个查询所用，因为其不包含SUM(UnitPrice*OrderQty*UnitPriceDiscount) 聚合。可再创建一个索引视图，来同时应付这两个查询。

视图2

CREATE VIEW Vdiscount2 WITH SCHEMABINDING AS
SELECT SUM(UnitPrice*OrderQty)AS SumPrice,
SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount))AS SumDiscountPrice,
SUM(UnitPrice*OrderQty*UnitPriceDiscount)AS SumDiscountPrice2,
COUNT_BIG(*) AS Count, ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID
  GO 
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount2 (ProductID)

使用这个索引视图，在丢弃Vdiscount1 后，这两个查询的查询执行计划现在包含：

•

一个聚集索引扫描，位于估计行数为266 的Vdiscount2 视图上

•

一个TOP 5 分类函数，基于ORDER BY 子句

查询优化器选择了该视图，因为虽然没有在查询中引用该视图，但其提供了最低的执行成本。

查询3

“查询3”与上述查询类似，但ProductID 被列SalesOrderID （未包含在视图定义中）所替换。这违反了条件：视图定义中表上的选择列表中的所有表达式必须派生自视图选择列表，以便使用查询计划中的索引视图。

SELECT TOP 3 SalesOrderID,
SUM(UnitPrice*OrderQty*UnitPriceDiscount) OrderRebate
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY OrderRebate DESC

必须用一个单独的索引视图来应付该查询。可修改Vdiscount2 以包含SalesOrderID；但是，结果视图将和原始表包含同样多的行，并不会通过使用基表提高性能。

查询4

该查询可生成每个产品的平均价格。

SELECT p.Name, od.ProductID,
AVG(od.UnitPrice*(1.00-od.UnitPriceDiscount)) AS AvgPrice,
SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p
WHERE od.ProductID=p.ProductID
GROUP BY p.Name, od.ProductID

复杂的聚合（比如：STDEV、VARIANCE、AVG）不能包含在索引视图的定义中。然而，通过包含（经组合）执行复杂聚合的一些简单的聚合函数，索引视图可用以执行含AVG 的查询。

视图3

该索引视图包含执行AVG 函数所需的简单聚合函数。在创建“视图3”后执行“查询4”时，执行计划将显示所用的视图。优化器可从视图的简单聚合列Price 和Count 派生AVG 表达式。

CREATE VIEW View3 WITH SCHEMABINDING AS
SELECT ProductID, SUM(UnitPrice*(1.00-UnitPriceDiscount)) AS Price,
COUNT_BIG(*) AS Count, SUM(OrderQty) AS Units
FROM Sales.SalesOrderDetail
GROUP BY ProductID
  GO 
CREATE UNIQUE CLUSTERED INDEX iv3 ON View3 (ProductID)

查询5

该查询与“查询4”相同，但包含一个附加的搜索条件。即使附加的搜索条件只从未包含在视图定义中的表引用列，“视图3”也将作用于该查询。

SELECT p.Name, od.ProductID,
AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,
SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p
WHERE od.ProductID=p.ProductID AND p.Name like '%Red%'
GROUP BY p.Name, od.ProductID

查询6

查询优化器无法对该查询使用“视图3”。添加的搜索条件od.UnitPrice>10 包含来自视图定义中表的列，但该列不显示在GROUP BY 列表中，而搜索谓词也不显示在视图定义中。

SELECT p.Name, od.ProductID,
AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,
SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p
WHERE od.ProductID=p.ProductID AND p.Name like '%Red%'
GROUP BY p.Name, od.ProductID

查询7

相反，查询优化器可对“查询7”使用“视图3”，因为新的搜索条件od.ProductID in (1,2,13,41) 中定义的列包含在视图定义的GROUP BY 子句中。

SELECT p.Name, od.ProductID,
AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,
SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p
WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10
GROUP BY p.Name, od.ProductID视图4
通过包含视图定义中的SumPrice 和Count 列以便计算查询中的AVG，该视图将满足“查询6”的条件。
CREATE VIEW View4 WITH SCHEMABINDING AS
SELECT p.Name, od.ProductID,
SUM(od.UnitPrice*(1.00-UnitPriceDiscount)) AS SumPrice,
SUM(od.OrderQty) AS Units, COUNT_BIG(*) AS Count
FROM Sales.SalesOrderDetail AS od, Production.Product AS p
WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10
GROUP BY p.Name, od.ProductID
  GO 
CREATE UNIQUE CLUSTERED INDEX VdiscountInd on View4 (Name, ProductID)

查询8

“视图4”上相同的索引也将用于在其中添加对表Sales.SalesOrderHeader 的联接的查询。该查询满足条件：查询FROM 子句中所列的表是索引视图的FROM 子句中的表的超集。

SELECT p.Name, od.ProductID,
AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,
SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p,
Sales.SalesOrderHeader AS o
WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID
AND od.UnitPrice > 10
GROUP BY p.Name, od.ProductID

最后两个查询在“查询8”的基础上进行了修改。每个修改后的查询都违反了优化器的条件之一，并且不同于“查询8”，无法使用“视图4”。

查询8a

“查询8a”(Q8a) 无法使用索引视图，因为WHERE 子句无法将视图定义中的UnitPrice > 10 与查询中的UnitPrice > 25 相匹配，而且UnitPrice 未出现在视图中。查询搜索条件谓词必须是视图定义中的搜索条件谓词的超集。

SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount))
AvgPrice, SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p,
Sales.SalesOrderHeader AS o
WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID
AND od.UnitPrice > 25
GROUP BY p.Name, od.ProductID

查询8b

注意，表Sales.SalesOrderHeader 不加入索引视图V4 定义。尽管这样，在该表上添加一个谓词将不允许使用索引视图，因为所添加的谓词可能会更改或消除加入下方“查询8b”所示的聚合的其他行。

SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount))
AS AvgPrice, SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p,
Sales.SalesOrderHeader AS o
WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID
AND od.UnitPrice > 10 AND o.OrderDate > '20040728'
GROUP BY p.Name, od.ProductID

视图4a

“视图4a”通过将UnitPrice 列包含在选择列表和GROUP BY 子句中，扩展了“视图4”。 “查询8a”可使用“视图4a”，因为将进一步筛选UnitPrice 值（已知大于10）以便只留下大于25 的值。以下是间隔归入的一个例子。

CREATE VIEW View4a WITH SCHEMABINDING AS
SELECT p.Name, od.ProductID, od.UnitPrice,
SUM(od.UnitPrice*(1.00-UnitPriceDiscount)) AS SumPrice,
SUM(od.OrderQty) AS Units, COUNT_BIG(*) AS Count
FROM Sales.SalesOrderDetail AS od, Production.Product AS p
WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10
GROUP BY p.Name, od.ProductID, od.UnitPrice
  GO 
CREATE UNIQUE CLUSTERED INDEX VdiscountInd
ON View4a (Name, ProductID, UnitPrice)

视图5

“视图5”在其选择和GROUP BY 列表中包含一个表达式。请注意，LineTotal 是一个计算列，因此本身是一个表达式。反过来，该表达式嵌套在对FLOOR 函数的调用中。

CREATE VIEW View5 WITH SCHEMABINDING AS
SELECT FLOOR(LineTotal) FloorTotal, COUNT_BIG(*) C
FROM Sales.SalesOrderDetail
GROUP BY FLOOR(LineTotal)
  GO 
CREATE UNIQUE CLUSTERED INDEX iView5 ON View5(FloorTotal)

查询9

“查询9”在其选择和GROUP BY 列表中包含表达式FLOOR(LineTotal)。通过对SQL Server 2005 中表达式的视图匹配的新扩展，该查询使用“视图5”上的索引。

SELECT TOP 5 FLOOR(LineTotal), Count(*)
FROM Sales.SalesOrderDetail
GROUP BY FLOOR(LineTotal)
ORDER BY COUNT(*) DESC

视图6

“视图6”存储月末三天中有关线项目的信息。这样可将这些行聚集在少量页面上，从而可以迅速应对这些天里对Sales.SalesOrderDetail 的查询。

CREATE VIEW View6 WITH SCHEMABINDING AS
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid,
ModifiedDate
FROM Sales.SalesOrderDetail
WHERE ModifiedDate IN ( convert(datetime, '2004-07-31', 120),
convert(datetime, '2004-07-30', 120),
convert(datetime, '2004-07-29', 120) )
  GO 
CREATE UNIQUE CLUSTERED INDEX VEndJulyO4Ind
ON View6(SalesOrderID, SalesOrderDetailID)
  GO 

查询10

下面的查询可匹配“视图6”，同时系统可生成一个计划，用于扫描视图上的VendJuly04Ind 索引，但不扫描整个Sales.SalesOrderDetail 表。此查询还说明了表达式等价（由于查询中日期的顺序不同于视图，而且数据格式也不同）和谓词归入（由于查询要求将结果的子集保存在视图中）。

SELECT h.*, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, d.rowguid,
d.ModifiedDate
FROM Sales.SalesOrderHeader as h, Sales.SalesOrderDetail as d
WHERE (d.ModifiedDate = '20040729' OR d.ModifiedDate = '20040730')
and d.SalesOrderID=h.SalesOrderID

视图7

开发人员有时还会发现使用索引视图强制专门的完整性约束很方便。例如，可通过索引视图强制约束：“除非列中存在多个0 值，否则表T 的列a 就是唯一的”。下方索引视图“视图7”就强制了这一约束。如果运行下面的脚本，其将成功运行直至最终的插入操作。该语句被禁止，因为其添加了一个非零重复值。

USE tempdb
  GO 
CREATE TABLE T(a int)
  GO 
CREATE VIEW View7 WITH SCHEMABINDING
AS SELECT a
FROM dbo.T
WHERE a <> 0
  GO 
CREATE UNIQUE CLUSTERED INDEX IV on View7(a)
  GO 
-- legal:
INSERT INTO T VALUES(1)
INSERT INTO T VALUES(2)
INSERT INTO T VALUES(0)
INSERT INTO T VALUES(0) -- duplicate 0
-- dissalowed:
INSERT INTO T VALUES(2)七、有关索引视图的常见问题问：为何对可创建索引的视图类型存在限制？
答：为了确保在逻辑上可对视图进行增量维护，限制创建维护成本较高的视图，并限制SQL Server 实施的复杂性。较大的视图集不具有确定性并与内容相关；其内容的“更改”独立于DML 操作。无法对这些内容进行索引。在其定义中调用GETDATE 或SUSER_SNAME 的任何视图就属于这类视图。
问：视图上的第一个索引为何必须为CLUSTERED 和UNIQUE？
答：必须为UNIQUE 以便在维护索引视图期间，轻松地按键值查找视图中的记录，并阻止创建带有重复项目的视图（要求维护特殊的逻辑）。必须为CLUSTERED，因为只有聚集索引才能在强制唯一性的同时存储行。
问：为何查询优化器不选取我的索引视图用于查询计划？
答：优化器不选取索引视图主要有三种原因：
(1) 使用SQL Server Enterprise 或Developer 版本之外的其他版本。只有Enterprise 和Developer 版本才支持自动的查询对索引视图匹配。按名称引用索引视图并包含NOEXPAND 提示，让查询处理器使用所有其他版本中的索引视图。
(2) 使用索引视图的成本可能超出从基表获取数据的成本，或者查询过于简单，使得针对基表的查询的速度既快又容易查找。当在较小的表上定义索引视图时，经常会发生这种情况。如要强制查询处理器使用索引视图，那么可使用NOEXPAND 提示。如果最初不通过显式的方式引用视图，这样做就可能要求重新编写查询。您可获得带有NOEXPAND 的查询的实际成本，并将之与不引用该视图的查询计划的实际成本相比较。如果两者的成本相近，那么您就可以认定用不用索引视图都不重要。
(3) 查询优化器不将查询与索引视图相匹配。重新检查视图和查询的定义，确保两者在结构上可相匹配。 CASTS、converts 以及其他在逻辑上不会更改查询结果的表达式可能会阻止匹配。另外，表达式规范化和等价以及SQL Server 执行的归入测试方面存在一些限制。可能无法显示某些等价表达式是相同的，或者逻辑上被其他表达式归入的表达式被真正归入，因此可能会错失匹配。
问：我每周更新一次数据仓库。索引视图使查询速度大大提升，却降低了每周更新的速度？该怎么办呢？
答：可以考虑在每周更新前丢弃索引视图，更新完后再重新创建。
问：我的视图存在重复项目，而我确实想对其进行维护。该怎么办呢？
答：可以考虑创建一个视图，按您所要的视图中的所有列和表达式进行分组，并添加一个COUNT_BIG(*) 列，然后在组合的列上创建一个唯一的聚集索引。分组过程可确保唯一性。虽然不是完全相同的视图，但可以满足您的需要。
问：我在一个视图上定义了另一个视图。 SQL Server 不让我索引顶级视图。该怎么办呢？
答：可以考虑手动将嵌套视图的定义扩展到顶级视图，然后对其进行索引（索引最低层的视图，或者不索引该视图）。
问：为何一定要对索引视图定义WITH SCHEMABINDING？
答：为了
•
使用schemaname.objectname 明确识别视图所引用的所有对象，而不管是哪个用户访问该视图，同时
•
不会以导致视图定义非法或强制SQL Server 在该视图上重新创建索引的方式，更改视图定义中所引用的对象。

问：为何不能在索引视图中使用OUTER JOIN？
答：当将数据插入基表时，行会在逻辑上从基于OUTER JOIN 的索引视图上消失。这会使执行OUTER JOIN 视图的增量更新变得相对复杂，而执行性能将比基于标准(INNER) JOIN 的视图慢一些。

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.