Advertisement

View (SQL View)

By: Amit Levi | Reviewed by Margaret RouseCheckmark | Last updated: March 14, 2022

What Does View (SQL View) Mean?

A view is a subset of a database that is generated from a user query and gets stored as a permanent object.

In a structured query language (SQL) database, for example, a view becomes a type of virtual table with filtered rows and columns that mimic those of the original database. While the table generated in a view is permanent, the data within fields is subject to change according to the source database.

Views allow data analysts to focus attention on specific types of information in a database. They are easy enough to create and save that even a citizen data scientist can use views to segment a large database into smaller, more manageable sections for analysis and close-up study.

Advertisement

Techopedia Explains View (SQL View)

A view is simply a structured query language (SQL) query stored as an object.

How a View is Created

A view allows the user to control the amount and specific criteria of the data they are pulling from a relational database.

For instance, the CUSTOMER_MASTER and ACCOUNTS_MASTER tables in the relational database of a commercial bank are frequently queried for customers and their account numbers. The following SQL query returns first name, surname, account number(s) and account types of customers:

SELECT c.first_name, c.surname, a.account_number, a.account_type

FROM customer_master c, accounts_master a

WHERE c.customer_id=a.customer_id

ORDER BY c.surname, a.account_number

Under normal circumstances, every time this query is run, it has to be parsed and loaded into the SQL optimizer. This consumes valuable time and compute resources.

If the query is saved as a view, however, then overhead activities will only be performed once at the time the view is created.

A sample SQL script to create a view is given below:

CREATE VIEW customer_accounts AS

(

SELECT c.first_name, c.surname, a.account_number, a.account_type

FROM customer_master c, accounts_master a

WHERE c.customer_id=a.customer_id

ORDER BY c.surname, a.account_number

)

The Limitations of View

While views have countless benefits when it comes to working with big databases in SQL, the view queries tends to fall short in a handful of aspects such as:

  • Location Restriction – Both the view and the source database must be in the same location storage-wise.
  • Lack of Compatibility – The user can either use standard SQL or legacy SQL queries to create a view, but not mix the two.
  • Read-only – When views are read-only, the user can perform minor calculations on data, but the results will have no effect on the original database.
  • Non-synchronized – Although objects in the source database are stored when a view is created, editing them will not affect the main database. As a result, future queries may be accurate in relation to the database, but not the view table and vice-versa.

Advertisement

Share this Term

  • Facebook
  • LinkedIn
  • Twitter

Related Reading

Tags

DatabaseData Management

Trending Articles

Go back to top