Querying using Views

Author by Nick Adams

A great tool to use for querying in SQL is the VIEW.  Views provide many advantages for code simplicity, security, and storage.  However, there is another layer of caution that should be noted when implementing Views and especially using Views for reporting and querying the database: performance and query flexibility.  Views can be very detrimental to the performance of your database and function support.

 

Before we crack open querying with Views, here are two very good reasons to use views.

Views are secure

Views can force security across the database so only certain users can hit that data.  You can give a user access to that View or schema to lock down what they can or cannot see and write your SELECT clause in that view accordingly.  If you have two tables that you JOIN together in a view such as Employees and Pay information but you don’t want to reveal salaries but do want to reveal pay frequency (See AdventureWorks2012 for examples), simply leave the Salary information out of the SELECT clause.

Views allow quick access to data

Since you already wrote the query for the View, if you need that specific data time and time again, you can access it with a simple SELECT clause.  Additionally, it can cut down on writing the same JOIN again and again by using the View in your query.  This can clean up and reduce code considerably.  There’s no denying that Views are convenient and there is nothing wrong with taking advantage of that when appropriate.

 

Now that we know the big high level advantages, let’s look at what the implications are of those advantages, specifically with performance and creating those Views.

Performance

One critical fact that you must know about Views is that they are NOT stored as a database object.  Only the SELECT is stored.  This means that every time you call a View, it must execute against the database and consume system resources to return your results.  As a result, if you have many joins in your View, you can cripple your database performance if you are using view after view after view in the same query.  The problem only compounds itself if you are joining those Views to other tables and other Views.  To get an idea of how extensive the issue can get, just take one look at this execution plan from AdventureWorks2012.

This execution plan is a SELECT * against a table and a View derrived from that table.  There is a single column that this View and table displays (the column is called “Group Name”) but look at the cost differences to get that single column.

Query flexibility and data consistency

Views have limited capabilities when being created.  Views do not support certain aggregate functions nor do they support ORDER BY.  They also do not support SELECT *, for better or worse.  Views also do not get new columns from the underlying tables without the View being rewritten.

Bringing the puzzle pieces together

Views are great for what they’re intended for.  But this is a case of what can they do vs. what should they be used for.  If there are very few security concerns regarding other users with your data or you have complete control over reporting and/or querying, use tables.  The performance is much better and often times, the data you are looking for can be retrieved by joining up a few tables.  If you use several Views to return data that can be retrieved from the underlying tables, you are wasting resources.  If those 3 columns for instance are on 3 easy to find tables, use them.  If you use 3 Views for instance and join them up, you might not be running just 3 joins.  You could really be running 15 joins, who knows, maybe even 30.  That is very taxing on your system.  Ultimately, this comes down to understanding your data.  Views are useful for coming back to that same query again and again assuming that your tables being used by the View are staying consistent and not being heavily modified.  But your tables are there for a reason.  Use them when you can.  The data is raw, up to date, and stable.  Views are by no means defective or dysfunctional but they have their times and places to be used.

 

In a later blog, we will look at Updateable Views and what can mean for you and your database.