In my previous article, we looked at how to use the CREATE VIEW SQL statement to create views. In this one, we are moving on and focusing on how to modify views. We will continue using examples on a sample database and data created in the first write-up so in order to follow along, head over and read the Creating views in SQL Server part before starting with this one.

Introduction

The primary goal will be to get familiar with the ALTER VIEW command used to modify views and change the output. A view is based on the result set from a query, and this command allows us to change the structure and definition of a query.

Ironically, before modifying a view, we will create another view with a bit more complex T-SQL using aggregates in it rather than having a simple SELECT statement that is pulling everything from a table. We will not go over the syntax again since T-SQL is exactly the same as in CREATE VIEW SQL statement except the fact that instead of the CREATE reserved keyword ALTER is used.

Creating view

As I mentioned earlier, let’s use the code from below to create a bit more complex view:

But before we run the script, we can again just highlight the SELECT statement and see what it returns as shown below:

Basically, what we are doing here is for each product in the Product table, we are fetching all the quantities and add them together per product. As you can see, we have our Long-Sleeve Logo Jersey product in different sizes and sold quantities. We only have four products in our table, so that’s why we’re selecting only the top three records.

Everything looks good, so we can execute the whole CREATE VIEW SQL statement to create the view with the SELECT statement that has SUM in it which is an aggregate:

The SUM is considered an aggregate because, in general, it adds the numbers together. Therefore, we also have the GROUP BY clause, followed by ORDER BY or otherwise, we’d run into some errors. In this particular case, this is what we’d get:

Msg 8120, Level 16, State 1, Line 4
Column ‘Sales.ProductID’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Once created, we can query this view by saying select everything from the name of the view and we should get the same result as before:

Hopefully, all this rings the bell from the first article. The point being, the complexity of a view can be as much as the complexity of the SELECT statement can get.

Modifying view

Let’s move on and take a look at how we can alter views. We will take the script of the first view as an example because it has a simple SELECT statement. If you remember the CREATE VIEW SQL syntax, a view can be modified by simply using the ALTER VIEW keyword instead, and then changing the structure of the SELECT statement.

To get started, in SQL Server Management Studio (SSMS) we can simply right-click the view from Object Explorer, and from the context menu navigate to Script View as | ALTER To | New Query Editor Window as shown below:

SSMS will take the existing structure of the view and generate the following code in a new query editor:

What we are interested in is the SELECT part of the code. Selecting everything is generally a bad thing. Why? For example, let’s say that we have an application using this view and that it’s relying on a specific output AKA the signature. There could be a problem if we change the underlying table e.g. add extra columns or remove some, etc. In other words, anything we do to the underlying table when saying select everything from (Employees.*) will shine through this view:

Therefore, let’s change the previously created view with the CREATE VIEW SQL statement by using the ALTER VIEW statement. Note that changing the view using this command does not affect dependent stored procedures or triggers and does not change permissions.

Previously, we generated a T-SQL script to modify our view within SSMS. This is the easiest and fastest way. But hey, if you like typing and doing it old school, just make sure that you’re connected to the appropriate database, type ALTER VIEW, followed by the name of the view, and then followed by an AS. After this, it goes the view definition:

As you can see, this is essentially the same thing as with the CREATE VIEW SQL syntax, we only change the definition of an existing view. So, let’s see what we can do with this SELECT statement and change the definition of the view to eliminate some potential problems.

For the purpose of this example, we can consider the code from below as one solution:

Before we run the script, let’s go through the SELECT part to see what we changed. Instead of fetching all columns from the Employees table, we are returning just three columns:

• Employees.EmployeeID
• FirstName
• LastName

Notice that the EmployeeID column is fully qualified because EmployeeID exists in both tables that we are referencing. On the other hand, FirstName and LastName only exist in the Employees table, so we don’t need to fully qualify those.

This is a very common mistake, and that’s why it’s always a good idea to run and check only the SELECT part in a batch to see what it returns. Simply remove the alias from the EmployeeID column and execute the SELECT part as shown below:

Msg 209, Level 16, State 1, Line 4
Ambiguous column name ‘EmployeeID’.

• Note: Now, that I’ve mentioned batches, bear in mind that CREATE VIEW SQL must be the only statement in a batch or the first statement in a query batch or you might get an error from SQL Server

In our case, you’ll see the error message “Ambiguous column name ‘EmployeeID’”. This is the SQL Server way of saying that we have referenced more than one column with the same name in the FROM clause.

If you remember the view’s definition, views are pretty much just virtual tables. So, if we head over to Object Explorer, expand the Views folder, then vEmployeesWithSales, and then the Columns folder, we should see the following:

When we initially created this view using the CREATE VIEW SQL statement, we specified that all columns from the Employees table should be retrieved. However, now rather than eight columns we only fetch three.

Conclusion

In this part of learning the CREATE VIEW SQL statement, we learned how to use the ALTER VIEW command to modify an existing view and change the output. I promised more in the first part, but rather than making this a long and boring article, we’ll continue our journey in the next one. We haven’t even touched how to use the DLM language for inserting data through a view, so that’s is what we’ll cover in the next part of the series.

I hope this article has been informative for you and I thank you for reading it. Stay tuned for the next one…

Table of contents

• Author
• Recent Posts

Bojan Petrovic

Bojan aka “Boksi”, an AP graduate in IT Technology focused on Networks and electronic technology from the Copenhagen School of Design and Technology, is a software analyst with experience in quality assurance, software support, product evangelism, and user engagement.

He has written extensively on both the SQL Shack and the ApexSQL Solution Center, on topics ranging from client technologies like 4K resolution and theming, error handling to index strategies, and performance monitoring.

Bojan works at ApexSQL in Nis, Serbia as an integral part of the team focusing on designing, developing, and testing the next generation of database tools including MySQL and SQL Server, and both stand-alone tools and integrations into Visual Studio, SSMS, and VSCode.

See more about Bojan at LinkedIn

View all posts by Bojan Petrovic

Latest posts by Bojan Petrovic (see all)

• Visual Studio Code for MySQL and MariaDB development - August 13, 2020
• SQL UPDATE syntax explained - July 10, 2020
• CREATE VIEW SQL: Working with indexed views in SQL Server - March 24, 2020