By Sudhir Karusala, Business Intelligence Technologist

Environment Scope: Microsoft SQL Server 2008 R2

The purpose of this article is to present an overview of Schema Binding, then explore both the potential benefits and hazards of utilizing indexed views.

WHAT IS SCHEMA BINDING?

Schema binding refers to the process of associating a database view to underlying tables in order to put indexes directly on the view. This may lead to great performance benefits when using the view; however, this tighter coupling is not without drawbacks.

RULES AND LIMITATIONS OF SCHEMA BOUND VIEWS

There are numerous rules and limitations that are enforced by the DBMS when working with schema bound tables. Consider the following rules and limitations when using Schema Binding:

  • Schema bound views must reference base tables only. They cannot reference other views.
  • Schema bound views must be in the same database and schema as the referenced base tables.
  • Multiple base tables utilize a two-part naming convention for specifying the columns for the view definition. One-part, three-part or four-part names are not allowed.
  • The “ANSI_NULLS” and “QUOTED_IDENTIFIER” options must be set to “ON” during the view creation process.
  • Base tables can’t be changed in ways that affect a schema bound view’s definition. This includes changing a column name or dropping the base table.
  • Indexes and Statistics in the schema bound view are updated when DML operations occur on base table data.

WORKING WITH SCHEMA BOUND VIEWS AND TABLES

There are some obvious advantages to using a view with indexes. Indexing a view directly can be much simpler for the developer or DBA; they need not worry about how changes to the base table may affect performance of other processes. Also, the SQL Server optimizer can have a tough time when working with very complex view queries, not always choosing the best execution plan. In such a scenario, the optimizer should produce more optimal execution plans if it can utilize the simpler view indexing.

However, there is a trade-off for the greater performance potential. Faster read performance is off-set by slower write performance. Whenever view-indexed data is modified in the base tables, indexes and statistics must be updated accordingly in the view. This may be insignificant in many cases, but if you’re working with large tables that produce a high volume of records in the view, the update time may be a serious drawback.

SCENARIOS

Indexed views can be advantageous for some scenarios and may be a liability in others. Some examples:

Data Marts

  • Data Marts are designed to rapidly access and output data, and indexed views can accommodate this expectation. For example, a schema bound view may assist in aggregating fact table information.
  • Updates are typically done in regular intervals, either nightly or periodically throughout the day. Since these intervals are easy to anticipate, the more resource intensive update operations can be easier to manage.

Transactional Systems

  • Since OLTP systems are expected to rapidly store information, the usefulness of indexed views is diminished because of the greater update times.
  • The additional complexity introduced by schema bound views may lead to inconsistent read times and even deadlocks.

CONCLUSION

Schema Binding is a powerful but complex tool. If leveraged properly, schema binding can lead to greater performance and more consistent data access in certain solutions.