Overview

• A view is a virtual table whose contents are defined by a query.
  • A view is essentially a query definition and does not contain any data.
  • A view is not a physical copy of data and does not contain any data itself.
  • Like a table, a view consists of a set of named columns and rows of data.
  • Unless indexed, a view does not exist as a stored set of data values in a database.
  • The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.
• A view acts as a filter on the underlying tables referenced in the view.
  • The query that defines the view can be from one or more tables or from other views in the current or other databases.
  • Distributed queries can also be used to define views that use data from multiple heterogeneous sources.
  • This is useful, for example, if you want to combine similarly structured data from different servers, each of which stores data for a different region of your organization.
• Views are generally used to focus, simplify, and customize the perception each user has of the database.
  • Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view.
  • Views can be used to provide a backward compatible interface to emulate a table that used to exist but whose schema has changed.
  • Views can also be used when you copy data to and from SQL Server to improve performance and to partition data.
• When the view is queried, it is expanded out to a query against the base table.
  • This means the same seek or scan will be used if you run the query against directly against source tables.

Views Performance

• The most appropriate use of views is to implement table and field-level security.
  • One of the most problematic uses of views is to ease and speed the development process. What typically happens is that developers will use a view, and filter over the top of the view with WHERE clauses.
  • If the view executes a full table scan, even a single record retrieved from a view accessing 1 million records from an underlying table will still effectively read all 1 million records.

SELECT * FROM ADVERTS;

Views Performance in SQL Server

• A View is merely a pre-defined query that can be treated in many ways as a table.
  • This allows DBAs or Database Developers to pull data from several tables and expose it as a single “virtual table”.
  • This has many advantages: the consumer of the view doesn’t have to have a detailed understanding of the database table layouts (schema); and a single addition or fix to the view then ripples out to all the consuming queries in one fell swoop.
• The behavior of views on SQL Server is different.
  • In classic databases, Views are slower because the database has to calculate them BEFORE they are used to join to other tables and BEFORE the where clauses are applied.
  • If there are a lot of tables in the View, then this process slows everything down.
  • However, this is not accurate on SQL Server.
• When a query is being broken down by the SQL Server’s optimizer, it looks at the fields within the select to see which of them are needed by the consuming query.
  • If it needs a given field, then it extracts it from the query definition, along with its table from the FROM clause, and any restrictions it needs from the WHERE clause or other clauses (INNER JOIN, GROUP BY, HAVING, etc.)
    • These extracted elements are then merged into the outer SQL statement.
    • The optimizer then joins the data together along indexes as best it can, just as it does with non-view elements, and then the entire query is run.
  • Therefore, the view is NOT pre-calculated just because it came from a view definition.

Common Performance Issues

• Sort Order: Queries often suffer from not being sequenced in an order that can easily be merged into the main query.
  • This causes the server to do extra work to sort the data returned by the sub-query before merging it.
  • In this circumstance, the data is pre-calculated so it can be sorted.
  • However, if the index that is used by the query orders the data correctly, these performance blockers go away.
  • These happen in views frequently since the writer of the view cannot anticipate which elements of the view will be extracted by the calling queries and, therefore, does not pay close attention to the underlying indices.
  • Fix: Watch your query plans and implement appropriate index to return data in the same order that is needed for the join.
• Inner Joins: When the view is broken down to see what fields on the SELECT are needed, and then the corresponding table from the FROM clause, it has to go one step further.
  • The query optimizer must consider anything in the WHERE clause that may throw out data.
  • In addition, inner Joins from the table in the FROM clause can also throw out data if the joined in table does not have a matching row.
  • Since the optimizer doesn’t know whether or not the Inner Join was used as a filtering device, it has to include it.
  • Very often, tables are joined in to show data that the consuming query doesn’t need, not just as a filter.
  • In these cases, the Inner Join only causes SQL Server to do more work for no good reason.
  • Fix: Whenever possible, limit the number of inner joins in the View definition.
    • Try to only use Inner Joins when you are certain most or all the consuming queries will need data from the joined table.
    • If there are many cases where the consuming data will not, consider multiple Views to service the various cases.
    • Left Joins are not used as filters.
      • If there is a left join, but there are no fields used in that table, it will be eliminated when the view is pulled in.
• Redundant Tables Calls: When you create a view, you can actually use another view as a source of data multiple times.
  • Since each of these views will have their query definitions pulled in as a Sub-Query, then it’s very possible that the same base table will participate in the query multiple times.
  • Fix: Try to limit to only using 1 view in a query.
    • Also, try to avoid using Views as a base table within another view.
    • If you find yourself needing info from multiple views, consider breaking form and joining in the base tables rather than pulling in the views.

Materialized Views (Indexed Views)

• A materialized view copies records from one or more underlying tables.
• Unlike a view, when a query is executed against a materialized view, the materialized view is physically accessed, rather than the underlying tables.
• Materialized views aggregate large data sets down to smaller sized hierarchical layers.