Oracle, MS-SQL, MySQL, DB2, PostgreSQL, and almost any other type of relational database – they all support a database object type called VIEW. In this article I’ll explain what is a view and list a few scenarios where using views can help.

WHAT IS A VIEW?

In simple terms, a view is a query. It can be almost any query, reading from one or more tables, sort of like a script that is stored inside the database engine. The query text is stored, not the data, and therefore it takes very little space (just the length of your query text). Once created, the view can be used like any table – it can be queried alone or in a complex query joining your view with other tables or views, and permissions to read from the view can be granted.

Example 1

A simple case where we read three columns from one table, and limit the rows returned.

create view female_teachers as

select first_name,last_name,subject

from teachers

where gender='female';

Then, selecting from the view is just like selecting from a table:

select * from female_teachers; -- selecting everything from the view

select * from female_teachers where subject='English'; -- selecting all female English teachers

Example 2

In this example we create a view that reads from two joined tables, sales and products, and aggregates the results by product category:

create view sales_by_category as

select p.category,sum(s.amount) as total_sales_amount

from sales s

left join products p on s.product_id=p.product_id

group by p.category;

WHEN SHOULD WE USE VIEWS?

Here are a few common use cases.

• Manage permissions – Hide some of the columns or rows from a table. Views can be very helpful if you want a school app to only read data about English students (“where subject=’English’) and only expose columns that do not include identifiable information (select student_id and not first_name,last_name, address etc.)
• Simplify complex queries. If you need to join several tables and deploy some business logic in a query, and then reuse these joins and business logic across multiple queries, a view can be helpful as it allows you to write the logic once and use repeatedly. For example, join your sales, products, stores and employees tables once to flatten your structure in a view, then read from the view.
• Mask connections to remote databases. In some environments we need to read from remote databases (Oracle database links or MS-SQL linked servers). A view can mask this from users/developers who won’t even know they are reading from a remote source.
• Beautify old-school table and column names. Old systems tend to have table and columns names that are hard to remember and use. employee_id, first_name and product_type make sense, but if your column names are more like EMPID01, NAME1ST and PRDTYP, you may want to create views that read the same data but rename column into nicer, readable names.
• Union / Union all between different tables with a similar structure. This is a specific case of simplifying complex queries – if you are reading from multiple tables with the same structure, union and union all can help simplify your query and allow you to read from one view instead of several tables.
• Workaround query limitations. I’ve recently came across a case where one of my clients tried joining a very large number of table in one query, and reached the limitation of the database engine they were working with. Ideally, we shouldn’t join too many tables together, but if needed, and the limit is reached, a view can help join a large number of tables, and then the view can act as one table in the original query. So if you need to join 90 tables and your database engine support 80, you can create a view that joins 80, and then join this view with the other 10 tables. This sounds a bit crazy but I’ve actually seen it in use.
• Create stubs for data sources that aren’t ready yet. One of my favourite use cases as I often work on BI projects where the ETL process may have not been written yet but the report author wants to start coding. If your report needs to read from a product dimension table and the table isn’t ready yet, you can create a dummy product dimension view(just selecting a few pseudo rows), start creating the report using the view, and when the table is ready, simply change the view to point to the new table.

SOUNDS GOOD? BE CAREFUL

A quick word of advice before you rush into creating and using views all over the place. Views can simplify your code and make it easier for developers who struggle with complex queries, but depending on what you do and how often, can cause severe performance issues.

If your users do not understand what’s ‘under the hood’ of the view, they may run complex queries that seem simple to them, and add unnecessary load to your servers. Let’s look at a simple example – you have created a sales view that joins 7 different tables to create a nice sales report. One of the columns in the view is product_name, originally read from the small product table. The product table has 42 records for 42 products, but the sales view has 5 million records, for the 5 million sales transactions in the system. If a developer uses the sales view to extract a distinct list of products (select distinct product_name from sales), they force a join between 7 tables and an expensive sort operation that looks for the 42 distinct values in a 5 millions result set. If that query is then used frequently in an app, this can cause a major head ache to your DBA, so please be careful.

I hope this article helps with some database views use cases. As always please comment below or email me if you have any questions.