SQL Server Brain Basher of the Week #016 - Views

During last 3-4 months, I have been continuously asking this question from many to understand how the fundamentals are known to everyone. A strong knowledge on fundamentals and concepts is the key for building efficient applications. Not only that, it takes us in the right path when solving problems. This question is based on Views, general views. Unfortunately, as per my rough calculation, only 30-40% were able to give the correct answer confidently. Here is the Brain Basher of the week;

Can we improve the performance of a SELECT query by creating a general view?

The answer I always expect is No but many say Yes. Of course, you can argue on this saying that it improves the performance in certain situations, but let me take that addressed later in this post.

Let's try to understand What is a View. Remember, View is just a definition, it is not a container that can hold data. We create views using either a single table or combining multiple tables. When they are called for data, the query written in the view is executed, just like we execute an ad-hoc SELECT statement. Therefore, theoretically, there cannot be any sort of improvements in terms of data retrieval with a View.

When I say No as the answer, the obvious question comes up: Why we need Views then?

Views help us in many ways. Main thing is security. It allows us to open only required columns from a table for outsiders without opening the entire table. For an example, a table that contains sensitive data such as Salary can be hidden by creating a view on top of the table with required columns and opening (or granting permission) the View to users instead of table. In addition to that, it reduces the complexity of a query with multiple joins. A SELECT query required by users (or developers) that needs many joins and filters can be made as a simple SELECT by creating a view with same query and let users to use the view instead of the query.

Okay, cannot we really improve the performance of it? Not with general Views but Index Views. A general View can be converted as an Index View by introducing a clustered index on it. Index View holds data based on the query written and when it is called, engine does not need to retrieve data from base tables and perform any calculations added to the query. However, the question discussed is not about Index View.