Bojan Petrovic
An executed CREATE VIEW SQL script showing data returned only for the SELECT statement in SSMS

CREATE VIEW SQL: Modifying views in SQL Server

March 5, 2020 by

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:

An executed CREATE VIEW SQL script showing data returned only for the SELECT statement in SSMS

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:

A message in result-set saying that CREATE VIEW SQL command completed successfully and showing the newly created view in Object Explorer

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.

An executed SELECT part within the CREATE VIEW SQL statement returning an error about missing the GROUP BY CLAUSE in an aggregate function

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:

An executed SELECT statement using a view in the FROM clause showing a list of products and total purchasing number

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:

"Script view as" option from right-click context menu in Object Explorer in SQL Server Management Studio

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:

An automatically generated script for altering a view from Object Explorer in the query editor

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’.

An executed ALTER VIEW SQL script showing error message about ambiguous column name returned only for the SELECT statement in SSMS

  • 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:

Object Explorer showing the difference between the list of columns in a table and a view

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

CREATE VIEW SQL: Creating views in SQL Server
CREATE VIEW SQL: Modifying views in SQL Server
CREATE VIEW SQL: Inserting data through views in SQL Server
CREATE VIEW SQL: Working with indexed views in SQL Server

Bojan Petrovic
SQL commands, SQL Server Management Studio (SSMS), T-SQL, Views

About Bojan Petrovic

Experienced QA Engineer with a demonstrated history of working in the computer software industry. Skilled in network technologies, technical support, Windows SQL Server, etc. Strong information technology professional with an AP graduate in IT Technology focused on Networks and electronic technology from the Copenhagen School of Design and Technology.

330 Views