CREATE VIEW SQL: Modifying views in SQL Server

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:

1 2 3 4 5 6 7 8 9 10 11

CREATE VIEW vTop3SalesByQuantity AS      SELECT TOP 3 --will only return first 3 records from query      Sales.ProductID,      Name AS ProductName,      SUM(Sales.Quantity) AS TotalQuantity      FROM Sales           JOIN Products ON Sales.ProductID = Products.ProductID      GROUP BY Sales.ProductID,               Name      ORDER BY SUM(Sales.Quantity) DESC;

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:

1	SELECT * FROM vTop3SalesByQuantity;

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

USE [SQLShackDB] GO      /****** Object:  View [dbo].[vEmployeesWithSales]    Script Date: 2/25/2020 10:49:32 PM ******/ SET ANSI_NULLS ON GO      SET QUOTED_IDENTIFIER ON GO      ALTER VIEW [dbo].[vEmployeesWithSales] AS      SELECT DISTINCT             Employees.*      FROM Employees           JOIN Sales ON Employees.EmployeeID = Sales.EmployeeID; GO

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:

1 2 3

ALTER VIEW vEmployeesWithSales AS     SELECT --statement that defines the view

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:

1 2 3 4 5 6 7 8 9

ALTER VIEW vEmployeesWithSales AS      SELECT DISTINCT             Employees.EmployeeID,             FirstName,             LastName      FROM Employees           INNER JOIN Sales ON Employees.EmployeeID = Sales.EmployeeID; GO

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.