Database View Video

This SQL video tutorial introduces what a database view is, its advantages and disadvantages, and how to create and drop a view. After you finish watching this video, you'll understand what a view is, the difference between a view and a table, and how to manage views in a database.

Embed this video using the code below

Video Transcription

Welcome to the 1Keydata SQL Tutorial. In this video, we will talk about database views. What they are, how to create them, and how to drop them.

So what is a view? And how is it different from a table? We start with a database with a series of tables, and you can build a view that references a table.

A view can be considered as a virtual table. It behaves like a table because it contains columns and rows just like a table, and all of your SQL statements that can be used on a table can also be used on a view. It is virtual because, unlike a table, which actually stores the underlying data, a view itself does not hold any data. A view is really just a definition of how you want to look at the data in the underlying tables.

You can build a view that points to a table, or you can also build a view that points to multiple tables. In fact, you can build a view that points to other views as well. Later in this video we will show you how to create a view.

There are several advantages to using views. The first is its ease of use. Often views are built to hide the complex underlying business logic, so instead of writing a SQL statement that joins multiple tables and have multiple filtering conditions, the end user can simply do a select star from a view.

The second advantage is that it saves space. A view is simply a definition statement and does not hold any data, so it doesn't require much room for storage. This is especially pronounced in today's era of big data, where row counts can often go to hundreds of millions or billions.

Another advantage of a view is data security. We can build different views for different user roles, so users can only see the data set they are supposed to see.

Finally, there is the speed to deployment aspect. Everything else being equal, creating and using a view is much easier than creating, maintaining, and using a table, and hence much faster to deploy. With a new table, the engineering team needs to figure out not just the table schema, but also how much data it will hold, and how frequently data needs to be updated. With a view, there is no sizing to worry about, and data refresh is not a concern, either. As a result, building a view will be a lot faster than a table.

As with everything else in technology, there are always tradeoffs, and indeed there are a few disadvantages to using a view. The first is query performance. Even though a query from a view may appear simple, often the underlying work that is required may be complex, since the database is still query against the underlying table.

Next, view definitions can get complex. Usually things are okay if the view is built directly on top of tables. However, because views can be built on top of other views, how a view is defined can quickly get out of hand.

Finally, even though views do not hold actual data, they are nevertheless still database objects and hence need to be managed just like other database objects. They should have naming conventions, regularly reviewed for deprecation, etc. All this will add to a DBA's workload.

So, in summary, views are convenient to use, but we should use them with caution, and we should try not to build multiple layers of views.

Now let's look at how to create a view. The syntax for creating a view is the same in all databases, and it is,

CREATE VIEW VIEW_NAME AS [SQL STATEMENT];

In this case, SQL statement can be any valid SQL select statement.

As an example, let's say you want to build a view on top of a table called daily_transaction, and you only want to include the rows where the sales column is greater than 100. To do this, you type in,

CREATE VIEW V_TOP_SALES AS SELECT * FROM DAILY_TRANSACTION WHERE SALES > 100;

If you can create a view, you'll want to also be able to drop a view, too. The syntax for dropping a view is simply,

DROP VIEW VIEW_NAME;

So for example, if you want to drop the view we have just created, type in

DROP VIEW V_TOP_SALES;

List of SQL Video Tutorials