SQL: What is a View?

Key Points

A SQL view is a virtual table whose contents are defined by a query.
Views act as a filter on underlying tables and don't involve data storage.
Views are used for data security, data aggregation, hiding complexity, and joining partitioned data.
Views are created using the CREATE VIEW statement followed by a SELECT query.

A view is a virtual table whose contents are defined by a query. It consists of a set of named columns and rows of data, which come from tables referenced in the query. The result of a view is produced dynamically when the view is referenced.

Unlike tables, views do not store data; they act as a filter on the underlying tables referenced in the view.

Views can be used to focus, simplify and customize the user's perception of the database. They can also be used as security mechanisms. For example, to grant users access to data through the view without directly accessing the underlying base tables.

SQL View Illustration — A view lets you aggregate the data from many tables into a virtual table.

SQL View Advantages

Here are the main advantages of SQL views:

Easy to create: Views are created by defining a SELECT query.
Simple maintenance: Views don't store the data so they are much easier to maintain.
Logical data independence: Views can provide logical data independence by separating the physical schema from the logical schema, making it easier to modify the physical schema without affecting applications.
Simplify application development: Views provide a consistent and simplified interface to the database, which reduced the amount of code.

SQL View Disadvantages

Here are the main disadvantages of SQL views:

Limited update functionality: Views can be restricted in terms of the updates that can be performed on them. This means that some updates might not be possible or might require complex workarounds.
Increased complexity: Views can make the database schema more complex, especially if they are used extensively. This can make it harder to understand and maintain the database.
Performance issues: Views can sometimes result in slower response times, especially if they are complex and involve multiple tables or complex joins.
Structure restrictions: The structure of a view is determined at the time of creation. This means that any changes made to the underlying tables may not be reflected in the view, and the view may need to be dropped and redefined.

How to create a view?

A view is created using the CREATE VIEW statement followed by the SELECT query:

sql

CREATE VIEW view_name AS
  SELECT columns
  FROM tables
  WHERE conditions;

When should I use a SQL view?

Views are used for:

1. Data security

Views can keep sensitive data private by omitting or obscuring sensitive columns and constraining which rows users can access.

2. Data aggregation

Views make aggregated data appear as though it is preaggregated and stored in the database.

3. Hiding complexity

Views shield end users from complexity by providing a simplified view of the data that hides the underlying complexity.

4. Joining partitioned data

Views can join partitioned data from multiple tables to make querying the data simpler and more efficient.

Are views faster than tables?

Yes, views can improve query performance when a clustered index is assigned to them. This is because the view's result set is materialized and persisted in physical storage in the database, which saves the overhead of performing costly operations at execution time.

The query optimizer can also select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan, which improves performance over querying the base tables directly.

However, most views are simple views that do not use this feature. They offer no performance advantage over querying the base tables directly. The performance improvements are greater when a unique clustered index is created on the view, as this reduces the search depth of the index tree and improves the speed of query execution.

How can I insert data into a view?

Before you can insert data into a view:

You must have the Insert privilege on the view.
The view must select from only one table.
View's SELECT statement cannot contain the DISTINCT keyword, GROUP BY clause, derived value or aggregate value.

If you meet all the above conditions, you can insert data into a view:

sql

INSERT INTO dbo.MyView (columnName) VALUES ('value')

Any columns in the underlying table that are unspecified in the view will receive either a default value or a NULL value if no default is specified.

Can views be nested?

Yes, a view can be nested using a different view. This means that the expression defining one view can include another view as a subquery.

For example, suppose we have two views V1 and V2. The expression defining V1 includes a subquery that references V2. When we query V1, the database retrieves data from V2 and uses it to compute the result of V1.

It's important to note that nesting views can have performance implications, especially if the nested views are complex or involve large amounts of data. In some cases, it may be more efficient to write a single query that combines the logic of both views rather than nesting them.

What happens if the underlying tables are dropped?

The view exists even after the tables that the view is based on are dropped.

If the underlying table(s) are dropped, the view will become invalid and unusable. Attempts to query the view will result in an error message indicating that the view cannot be found or accessed.

What is a Materialized View?

A materialized view is a database object that contains the results of a query. Materialized views are particularly useful when dealing with large datasets or complex queries that take a long time to execute.

Unlike a regular view, a materialized view is created by taking the results of a query and storing them in a physical table.

This table can then be used as if it were a regular view, but with the added benefit of greater performance since the query doesn't have to run each time.

Published on: Mar 19, 2023