A very common object you will see in SQL Server databases are Views. It is important to know what a View is, how to create one, and how they can help your user experience.
Views are part of the Top 6 tools you should know for writing AWESOME Select statements. If you want to learn more about other extremely common and useful query tools, check out that tutorial.
In SQL Server, a View is known as a “table expression”. If you would like a quick rundown on table expressions, I have a full introduction here:
SQL Server Table Expressions: The Ultimate Guide for Beginners
We’ll start off this tutorial with a simple question: Given the following two queries that return identical information, which are you more likely to remember how to write from scratch?
This one?:
Or this one?
Congratulations, now you understand the idea behind a View
In this tutorial, we’ll discuss these topics:
- What is a View in SQL Server?
- How to create a View in SQL Server
- How to alter or drop a View in SQL Server
- Can I perform INSERTS/UPDATES/DELETES on a View?
- What are the limitations on a View?
- Tips, tricks, and links.
Also, the following FREE Ebook will definitely help you:
FREE Ebook on SQL Server Table Expressions!
This Ebook provides an excellent summary of everything you need to know about the different table expressions we can create in Microsoft SQL Server, including Views. It will be a great resource for you to reference throughout your career as a data professional. Make sure you download it today!
Let’s take a look.
1. What is a View in SQL Server?
A View is a virtual table object that pulls column data from one or more tables. Views can be used to store complex SQL queries so that they can be ran repeatedly using simple commands.
In our example, both queries pull information about repeat customers. It gives us some data about the customer and how many items they have bought.
That first query needed to JOIN 3 tables together to get all that information (which, believe it or not, is not that many!).
Sure, if you can remember all the tables and the columns, you could reconstruct the query every time someone asks you for that information.
I don’t know about you, but I don’t even remember what I had for breakfast two days ago.
Or, you could create a View that pulls all that same data, and just remember the name of that View to get the same information.
2. How to create a View in SQL Server
To create a View, you need to run the ‘CREATE VIEW‘ statement against the SELECT statement you are trying to save. Here is the basic layout:
CREATE VIEW viewName AS <SELECT Statement>
For example, here it the CREATE VIEW statement I ran against my complex SQL query:
Notice all I really did was add the words ‘CREATE VIEW RepeatCustomerInfo AS‘ to the top of my SELECT statement
Once that statement is ran, you can basically run the same SELECT statement much easier now. Instead of needing to run that full complex statement, you can just query the new ‘RepeatCustomerInfo‘ virtual table.
You would query the View just like you would query any other table!
If you wanted to restrict the results of the View further, you can easily do that. For example, if you wanted to see customers who bought more than two items, you can do it very easily by adding a simple WHERE clause:
It’s worth repeating: You query the View just like you would query any other table.
Before we move on, I do want to point out that I left out some options that you can add as part of the definition of the View. For example, you can add an option to make the definition of the View encrypted to certain people. We will talk about those options in the Tips, tricks and links section of this tutorial.
3. How to alter or drop a View in SQL Server
It’s simple. If you want to alter the definition of a View, you would use the ‘ALTER VIEW‘ statement, like so:
Notice all I really did was change the word ‘CREATE‘ to ‘ALTER‘. Of course, the definition of the View has a slight change (can you spot it?)
But what if you created the View, then shut down your query window (in other words, the definition of the View is no longer directly in front of you). How can you easily load up the definition of the View to make changes?
If you need to alter a View that already exists, you can find the View in the Object Explorer and alter it from there. You would just navigate to the View under the Views folder of your database, right click and choose Script View As | ALTER To | New Query Editor Window, basically like so:
This will open the View in a new query window. The View will be ready for you to ALTER, as shown here:
To drop a view, just use the ‘DROP VIEW‘ command, like so:
Notice you don’t need to specify the definition. Just the name of the View.
4. Can I perform INSERTS/UPDATES/DELETES on a View?
Yes you can, but it can be complicated to do sometimes.
Remember, if we look at the definition of the View, we see that it pulls columns from several different tables. It also aggregates a column, resulting in the ‘TotalItemsBought‘ column of the View.
Basically, this means that if you wanted to insert a row into the ‘RepeatCustomerInfo’ View, you would basically need to break up the insert into several smaller inserts into each of the individual tables involved in the View.
The same is true for updating and deleting rows in a View.
The topic of running INSERT/UPDATE/DELETE statements against a View is outside the scope of this beginner tutorial, but understand that yes, it can be done. I wrote a full tutorial on the topic you should check out:
Updatable View in SQL Server: A How-To Guide
5. What are the limitations on a View?
There are only a few limitations on a View.
Your View can return only one result set
You cannot do the following, for example:
Even a second SELECT statement as simple as “SELECT 1” will break it.
I need to make something clear, however.
Your View can have more than one SELECT statement, but the View must return a single result set. The example above returns two result sets.
Think of the UNION set operator. With that operator, you can combine the results of two or more SELECT statements into a single result set. Something like that would be fine in a View. Using UNION, you have more than one SELECT statement, but it returns a single result set.
The ORDER BY clause
Another limitation on a View is on the ORDER BY clause. In the definition of a View, if you want to use an ORDER BY clause on the underlying SELECT statement, it must be accompanied by a TOP or OFFSET keyword in the SELECT list. TOP and OFFSET would limit the number of rows returned by your underlying SELECT statement.
If you don’t want to limit the number of rows returned in that way, you cannot use the ORDER BY clause in the definition of your View.
You can, however, use a presentation ORDER BY clause when you call the View, like this:
6. Tips, tricks, and links
Here is a list of tips and tricks you should know when working with Views in SQL Server.
Tip # 1: There are several View options you can use when creating your View
I mentioned earlier that there are some options that you can use as part of the definition of the View. Those options are:
WITH SCHEMABINDING
While the View exists, this means you cannot change the schema of any of the underlying tables of the View. For example, in my View definition, I reference the ‘Phone‘ column of the ‘ContactInfo’ table. If I use the ‘WITH SCHEMABINDING‘ option on the View, I would not be allowed to change the name of the ‘Phone‘ column to ‘PhoneNumbers‘, for example. This would be an alteration of the schema of the ‘ContactInfo’ table, which again would not be allowed.
WITH ENCRYPTION
This will encrypt the definition of the View, so that unauthorized people cannot see the definition. This would be a setting a DBA might want to set on the View, for example.
WITH VIEW_METADATA
This changes the way applications that access the View see the definition of the View. An application might access the View and see the underlying tables of the View. If instead, you want the application to see the definition of the View itself, and not of the underlying tables, you would use ‘WITH VIEW_METADATA‘.
If you want to use any of these options in the definition of the View, you would follow this layout:
CREATE/ALTER VIEW viewName <option name> AS <SELECT statement>
For example, here is the definition of our View using the ‘WITH ENCRYPTION‘ option
The WITH CHECK OPTION
Another option you can specify is ‘WITH CHECK OPTION‘. This option goes back to the idea of changing or inserting rows in a View.
With this option, you cannot change or insert a row into a View that would violate the filtering of the View.
For example, let’s look at the following View:
Here is a query to this View:
In the definition of the View, notice we have a filter (aka a WHERE clause) that restricts us to seeing Employee details for Employees based out of USA only.
Certainly, in the Employees table, there are Employees from other Countries. But again, in the View, we only return USA Employees.
What if we wanted to change Employee number 14 to be from ‘UK‘? As it stands now, I can do that:
In other words, we just updated a row from one we can see to a row we cannot see! Notice there is no Employee # 14 in the result set now.
If we didn’t know any better, it would look like we accidentally deleted a row. Maybe I’m an end user, who only has access to the View and not the underlying Employees table. Again, from that perspective, one might think the row was deleted.
If we have the WITH CHECK OPTION set up, we cannot change a row from one we can see to one we cannot see.
To use this option, you would put the words ‘WITH CHECK OPTION‘ directly after your SELECT statement within your View definition.
After altering the View to include this setting, you now get an error message if you try to make the same change to Employee 11:
My screenshot cuts off the full error message, so here it is:
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
Tip # 2: All columns in the definition of a View must be given a name.
Take a look at the columns in the definition of the ‘RepeatCustomerInfo‘ View:
We have one column where we replace a blank phone number with “–” via the COALESCE function, and use the SUM aggregate against the Qty column. If we were just writing the same SELECT statement outside of the View, for example, we could absolutely leave off column aliases, resulting in those two columns without a name in the result set. In that scenario, we’re not required to name those columns. However, in a View, we are required to name all of our columns.
Tip # 3: It’s possible to run DML statements against a View
If you need to know how to run DML statements (like INSERT, UPDATE, or DELETE) against a View, you should check out the full tutorial on Updatable Views.
Links
Here is a link to Microsoft’s documentation about Views: SQL Server Views
Next Steps:
Leave a comment if you found this tutorial helpful!
Don’t forget to download your FREE Ebook:
FREE Ebook on SQL Server Table Expressions!
This Ebook provides an excellent summary of everything you need to know about the different table expressions we can create in Microsoft SQL Server, including Views. It will be a great resource for you to reference throughout your career as a data professional. Make sure you download it today!
As mentioned earlier, I have a full tutorial on Updateable Views. If you need to know how to INSERT/UPDATE/DELETE data against a View, you can learn how to do it by reading the tutorial! Check it out here:
Updatable View in SQL Server: A How-To Guide
Also, as mentioned earlier, Views are part of the Top 6 tools you should know for writing AWESOME Select statements. If you want to learn more about other extremely common and useful query tools, check out that tutorial.
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, leave a comment. Or better yet, send me an email!
