Database definition

What is sight? – Definition from Techopedia

What does view (SQL view) mean?

A view is a subset of a database generated from a user query and 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. Although the table generated in a view is permanent, the field data is subject to change depending on the source database.

Views allow data analysts to focus 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 further analysis and study.

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 data retrieved from a relational database.

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

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

FROM client_master c, account_master a

WHERE c.customer_id=a.customer_id

ORDER BY c.name, a.account_number

Under normal circumstances, each time this query is executed, it must be parsed and loaded into the SQL optimizer. This consumes valuable time and computational resources.

However, if the query is saved as a view, the general activities will only be executed once when the view is created.

An example 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 client_master c, account_master a

WHERE c.customer_id=a.customer_id

ORDER BY c.name, a.account_number

)

The limits of sight

Although views have countless advantages when it comes to working with large databases in SQL, view queries tend to fall short in a handful of aspects such as:

  • Location restriction – The view and the source database must be in the same location in terms of storage.
  • Lack of compatibility – The user can either use standard SQL queries 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 the data, but the results will have no effect on the original database.
  • Not synchronized – Although source database objects are stored when a view is created, modifying them will not affect the main database. Therefore, future queries may be accurate against the database but not the view table and vice-versa.