SQL views are another powerful database object we have at our disposal. In the previous two articles of this series, we’ve talked about user-defined functions and user-defined procedures and showed simple examples of how to use them. Today, we’ll do the same for the SQL views.

The Model

The first thing we’ll do is to remind ourselves of the database model we’ll be using today (and we’re using throughout this series):

We’ll use it to create queries over a single table, and that will be the country table.

What Are Database Views?

We’ve talked about user-defined functions and stored procedures in the previous two articles. If you’re into programming, I guess you’ve met them or at least their counterparts in some programming languages because they are pretty common as a concept and widely used. That is not the situation with views.

They are much more specific to databases. The main idea is to create a database object where we’ll “store” the result of the query. The word “store” is maybe not the best one. We’ll store this structure and the query it contains, and we’ll run this query when we reference this structure.

If you ask yourself why, there are a few good reasons, and we’ll talk about the advantages and disadvantages later.

If you ask yourself, why we have a new database object to store just a single query, and why not to write that query or use a procedure, this could be a short answer to that question. It’s important to understand that when you have a database view, the query is stored in that view and you don’t need to write it from scratch. Also, when compared to procedures, views are generally simpler (you don’t pass parameters, you have only one select statement), and you can do some operations procedures that wouldn’t allow you to do it (insert, update, delete).

Let’s move to the examples now.

SQL Views – Simple Example

Let’s create a very simple view. Our view shall return all data from the country table. The code needed is:

Similarly to the creating procedures in the previous article, the first line contains the DROP statement (to delete a view if it exists) and after that goes the code that creates a view. After running these statements, the view is created and we can see that in the Object Explorer under Views:

Now, we’ll use this view in the select statement. We’ll go as simple as it’s possible.

You can notice that the result is the same as it would be if we ran the query that is in the view. Also, in our select query, we’ve used the view as we would use any other regular database table.

SQL Views – Insert, Updates & Delete

If we can select from the view, this leads to the next question. Can we use the view to insert new rows, update or delete existing? And the answer, in SQL Server, is – “yes”.

So, let’s insert a new row using the view we’ve just created:

The operation completed successfully, and we’ll check if the change in the table is as expected.

You can notice that we have 1 more line in our table, so the insert using view was performed successfully.

The next thing we’ll try is to update the existing row using the view. We’ll update the row we’ve inserted last, using the following statement:

You can notice that we’ve updated value for only one column from the view. Once more, we’ll check what happened in the table, selecting from the view:

We can notice that the value changes.

The last thing we’ll do using the view is to delete an existing record. To do that, we’ll use the following statement:

We’ll again check the contents of the table using the combination of select and view.

You can notice that, as expected, the row was deleted.

After performing insert, update, and delete, we can only conclude that SQL Server allows us to perform all operations when we’re using views. Of course, for these operations, your view should contain only one table.

SQL Views – Advantages & Disadvantages

Like stored procedures, SQL views also have a number of advantages. I’ll try to list the most important ones here:

• Security – I’ll put security in the first place because, similarly to procedures, you can define who can use a view and how. That same user doesn’t have access to tables used in the view, but only to the view. This way, you can protect sensitive details stored in the table and expose only the ones you want the user to see
• Easy to use (for the end-user) – While you might know how to write cool and complex queries, most business users are not interested in that. They just want to get the data. Putting your complex query in the view and allowing business users to use the view, shall hide the complexity of the query and return only the columns they need. You’ll use views as a way how to store your complex code. Also, be aware that you should name your views consistently and logically, so anyone can understand what the view does, simply from its’ name
• Following business rules & consistency of business logic – This is related to the previous bullet. If you have specific reports, business users need, you can create a SQL view for every single report. All who need a certain number can simply run this view. If something changes in the reporting requirements, you’ll simply change the view, and all who use it shall immediately feel the effect of that change
• Use them to make database changes – Imagine a situation where you want to remove the table, replace it with few tables, or simply changing a table name. In case you do that, there is a great chance you’ll mess up the code somewhere, where this table was used. If you want to prevent that, you could create a view with the same name as the old table had. While this is a fix, this could prove to spare a lot of time
• Views don’t take space – Views are used to store your code, not complete tables. Each time you call a view, you’ll run the related query. Therefore, you don’t lose disk space on views

It would be great that we have only advantages, but as it’s usually the case with the most things in life, views also come with some disadvantages:

• Database changes & views – If you remove an attribute used in the view, the view won’t work. That is the same thing as if you’re trying to run a query using the name of the non-existing column. This is not a big deal if you’re using views only for reporting, because end users will pass the info that their report is not working as expected. In case you’re combining views with insert, update, or delete (some DBMSs allow that) operations, you’ll have a bigger issue
• Performance – This could theoretically be a problem because business/end users are usually not aware (and there is no reason why they should be) of what you did. If the query stored in the SQL view is complex and/or not-optimized, it will use a lot of resources and time, and this will lead to all possible issues long queries can cause. We’ll talk more about that later in the series. Still, a business user has no idea of that and could be confused or try to use your view multiple times, etc.

So, When to Use SQL Views?

I’m personally not a big fan of views because I like to store my code in the stored procedures. Still, there are occasions where they can be more than useful. My recommendation would be to use them when creating reports containing a complex select query, grabbing data from multiple tables. If the DBMS you’re using allows that, you could use views for other commands (insert, update, delete) too, and build your system in the “ORM (object-relational mapping) style”.

Table of contents

• Author
• Recent Posts

Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer.

His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby...

You can find him on LinkedIn

View all posts by Emil Drkusic

Latest posts by Emil Drkusic (see all)

• Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
• Learn SQL: Dynamic SQL - March 3, 2021
• Learn SQL: SQL Injection - November 2, 2020