SQL Fundamentals - Tables v. Views

Something that gets overlooked a lot is knowing when the proper time to use a Table and when to use a View when working with data. They both use different methods of utilizing data and they both have different efficiencies when they are used. Using the right item can save you money or time.

Let's start with an example. We will use the following tables. The first table is a list of customers. The second table is a list of locations. The purpose of our table will be to get the Home Locations for the customers and provide their first name, last name, city, and state.

The code for creating tables and views are virtually identical. In these examples, the system was Teradata. Oracle and SQL Server versions will be slightly different. This first segment of code is to create a table.

CREATE MULTISET TABLE home_loc_tbl AS(
SELECT
  a.FName, 
  a.LName,
  b.City,
  b.State
FROM
  Customer AS a
  JOIN Location AS b
    ON a.CustNum=b.MbrNum
    AND b.LocType='Home'
)WITH DATA
PRIMARY INDEX(Lname, FName);

This next segment of code is how you would do the same thing,but creating a view instead.

CREATE VIEW home_loc_vw AS (
SELECT
  a.FName, 
  a.LName,
  b.City,
  b.State
FROM
  Customer AS a
  JOIN Location AS b
    ON a.CustNum=b.MbrNum
    AND b.LocType='Home'
);

With tables or views, they are queried or used in your SQL code identically. And when we query them both, it seems we are getting the same result.

Even though the results are the same in this situation, we will see this is not always the case. Let's now say that after our table and view have been created, Member Number 1 moves to from Texas to Arizona and has his information updated. This now gives us the source data of:

Now, when we query both the table and the view, we will get different results.

Why did this happen? When you create a table, it takes all of the data the query compiled and placed it into the table. The view on the other hand only stores the logical connection you used to gather the information. That means, any time you query a view, it is actually going back to the source tables and pulling the information each time.

This gives views certain advantages, and certain drawbacks.

Some of the good points are that it simplifies code. If you have ever tried to troubleshoot SQL code and the author had used subqueries and re-used the same alias names from the main parts of the query, that can be a headache. But having the entire subquery being a view makes it look like and work mostly like a table that most people are familiar with. Another bonus is that with it only being a logical connection, any time the source data changes, the data compiled by the view is the most up to date. And finally, views do not take up much space. They only need to store the code for the query used to generate it. This will save money for organizations that are being charged for space utilization.

Though, there are times when using a view is a drawback. You can't store historic data in a view. When you query it, it is always what it is at that moment. Another drawback is that when you query the view, it has to go through all of the processing to compile the data. So, while it saves on disk space, it will add to CPU utilization which can be another cost factor.

This then may make you wonder when to use what type of item.

Use Tables:

• If you need permanent storage of information
• If you access the set of data repeatedly and changes are infrequent. (Saves CPU Utilization)

Use Views:

• If the data changes rapidly
• If you infrequently access the view. (Saves Disk Utilization)