5 Reasons Why We Should Use SQL Views

SQL views are very powerful when used properly. They allow us to store our SQL queries into virtualized containers (aka virtual tables) in our database. They also contain rows and columns just like a traditional table. The main difference is that views don’t store any data, while tables do. Views are only able to store the underlying SQL statement to retrieve a result set. There isn’t an option to insert or update data in views since they only act as a reference to data in physical tables.

Why use SQL views? There are several reasons to do this.

1) Reusability

Views allow us to easily access our SQL queries without having to re-write our SELECT statements repeatedly. For example, we would create a view if we had multiple tables that we commonly join to. These views may have conditional logic or aggregations we don’t want to constantly rewrite. Just be careful with this kind of approach since it can often result in performance degradation if not used properly. Many database users begin to treat views as if they were tables. That can be a problem if the user doesn’t understand the underlying query within the view.

2) Complexity

We can use SQL queries to build in complex business logic with certain formulas or combination of data joins and filters. SQL views are a great away to store these complexities and have accessibility and reusability (#1).

3) Business Naming

The option for changing business naming in reporting systems doesn’t always exist. As an alternative we can create views to rename columns for their business purpose. Often, database column names don’t line up to the terminology a business wants to use. We may even give power-users access to views with column names aligned to that business meaning (if they don’t already).

4) Security

Views are another way to limit visibility into a database. We may not want to provide certain users access to actual tables and rather give them access to views that would have a slice of the data rather than the full set. As an example, we may have teachers at a school that need access to their student data. We only want to give them access to view their own students. Views can be an effective method at providing the visibility they need.

5) Purely Reference

There may be times where we only create views as a data diagram reference – for example, if we’re lacking data reference documentation. One common scenario is when subject matter experts (SME’s) of a database move on from a company and don’t provide sufficient documentation. This leaves the next person picking up the pieces to rely on database objects such as views to understand database logic and table relationships. If foreign keys have been created on tables we could reference those as well. Unfortunately, many times these keys don’t get created for performance reasons.

Final Thoughts

We can see why views have an important place in a database environment. They offer tons of flexibility and can be very useful in maintaining data relationships and business rules. We should also be careful as to not become “view dependent”. Many times views are used as shortcuts to save time and cut costs. Sometimes there’s no choice, but in my experience shortcuts in the data world never seem to work out. Someone pays down the line. We just have to be careful. It’s just like a saw. It’s a super useful tool, but can be dangerous in the wrong hands!