SQL View

A view is a virtual table. A view consists of rows and columns just like a table. The difference between a view and a table is that views are definitions built on top of other tables (or views), and do not hold data themselves. If data is changing in the underlying table, the same change is reflected in the view. A view can be built on top of a single table or multiple tables. It can also be built on top of another view. In the SQL Create View page, we will see how a view can be built.

Views offer the following advantages:

1. Ease of use: A view hides the complexity of the database tables from end users. Essentially we can think of views as a layer of abstraction on top of the database tables.

2. Space savings: Views take very little space to store, since they do not store actual data.

3. Data security: Views can include only certain columns in the table so that only the non-sensitive columns are included and exposed to the end user. In addition, some databases allow views to have different security settings, thus hiding sensitive data from prying eyes.

4. Speed to deployment: Sometimes you want a table that is built on top of the existing data set, but in order to make this happen, you'll need to work with your engineering team to go through the process of defining, populating, and maintaining a new table, and this can be time-consuming. Instead of going that way, creating a view may be a much faster way to go. Since a view doesn't hold actual data and doesn't need to be refreshed on a regular cadence, it can be deployed much faster, and sometimes you might even be able to create the view yourself.

Like many things, there are also tradeoffs that come with using views. Below are a few disadvantages of using views:

1. Query performance: While a query into a view is usually simple to write and easy to understand, the actual query that the database has to execute will be as complex as the view definition because the query still needs to occur at the table level. As a result, query performance may suffer.

2. View definitions can get complex: When a view is built directly on top of tables, it is usually relatively easy to decipher where everything came from. However, when a view is built on top of other views, understanding that view contains can become very difficult, especially if those views themselves are also built on top of yet another set of views. The author once had an opportunity to slice through four levels of views, and that was certainly not a fun exercise!

3. Increased database management load: Views are also database objects like tables, so even though views do not hold actual data, they still need to be managed carefully, just like tables. Having too many views can easily increase the complexity of managing a database.

The rule of thumb is that while views are quite useful, we must be careful not to overuse it. Also, remember it is usually not a good idea to build a view on top of other views.