Alter view in SQL Server

In this SQL Server tutorial, we will discuss how to alter view in SQL Server. We will discuss various ways and examples to modify a view. The following are some examples that we are going to discuss.

• Can we alter view in SQL Server?
• Alter view permission in SQL Server
• Alter view query in SQL Server
• Alter view in SQL Server management studio
• Alter view add column SQL Server
• Alter view column data type in SQL Server
• Alter view name in SQL Server
• Create or Alter view in SQL Server
• If exists alter view in SQL Server
• Alter view with schemabinding in SQL Server

If you have not yet installed SQL Server, first install SQL Server 2019.

Can we alter view in SQL Server?

Yes, we can alter a view in SQL Server. There are multiple ways to alter a view in SQL Server. We have to use the ALTER VIEW command to alter a view in SQL Server.

However, we can also alter a view using SQL Server management studio. Using SQL Server management studio makes it easy to work with any object in the database. But before that let us discuss the permissions required to alter a view in SQL Server.

Alter view permission in SQL Server

To alter a view in SQL Server, you need to have ALTER permission on the view. You will also need to have certain permissions on the underlying objects according to the operations you are performing.

For example, if you are referring to a SELECT statement on a table in your view, you need to have SELECT permission on the table.

Similarly, if your view is referencing any other objects like views or functions, you need to take care of the permissions on those objects.

Also, check: How to delete a view in SQL Server

Alter view query in SQL Server

In SQL Server, you can use the ALTER VIEW statement to modify or alter a view. The general syntax of the ALTER VIEW statement is:

ALTER VIEW <view name>
AS
<SELECT statement>

For example, I have created a view that retrieves some selected data from a table.

USE BackupDatabase
GO
CREATE VIEW dbo.SelectView
AS
SELECT [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,[Size]
      ,[Weight]
  FROM [BackupDatabase].[SalesLT].[Product]

For now, this view is selecting all the records because I have not used the WHERE condition. Suppose I want to alter this view so that it returns a record according to a specified condition, I will use the ALTER VIEW statement as:

USE BackupDatabase
GO
ALTER VIEW dbo.SelectView
AS
SELECT [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,[Size]
      ,[Weight]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE ListPrice > 500

Now if you retrieve the data using the view, you can see that the records are returned according to the specified condition.

USE BackupDatabase
GO
SELECT * FROM dbo.SelectView

In this way, you can modify a view in SQL Server using the T-SQL query method.

Read: SQL Server bulk insert from CSV file

Alter view in SQL Server management studio

If you are connecting to your database using SQL Server management studio, you can alter a view from the object explorer window also.

• To alter a view in your database, navigate to your database in the object explorer window.
• Expand the database and right click on Views and navigate to your view.
• Right click on the veiw that you want to alter and select Design.

• You will see a window where you can design your view graphically without any T-SQL query.
• Here you can add or remove tables, select or unselect columns, make joins etc very easily.
• Here you can also view or edit the resulting T-SQL query. This means you can alter your view graphically as well as with the query simultaneously.

• Once you have made all the modifications, you can save the view from the File menu or simply press Ctrl+S and the view will be altered.

In this way, you can alter a view in SQL Server using SQL Server management studio.

Also, read: SQL Server function return table

Alter view add column SQL Server

If you want to add a column into your view, you have to write the whole view body again and add the desired column into it.

If your view is very complex, you may find it difficult to do this. To make this process easy, you can script your view in SQL Server management studio and you will get the original view body, In this body, you can add the desired column. Let us see how to do it.

• Connect to your database using SQL Server management studio.
• In the object explorer window, expand your database and navigate to your view that yu want to modify.
• Right click on the view and click on Script View as, then click on ALTER TO and then click on New Query Editor Window.

• You will see the body of the view in a new query editor window.

• Here you can add or remove any column in your SELECT statement.
• Once you have added the column, you can just run the query and the view will be altered.

Thus, you might have learned how to add a column to your existing view in SQL Server.

Read: How to select latest record in SQL Server

Alter view column data type in SQL Server

When you create a view, the data types of the columns in the view are the same as they are in the underlying tables.

But, in certain scenarios, you may want some column value of a different data type. In that case, you can convert or cast the data type of the column using the CONVERT or the CAST functions respectively.

For example, I have created a view as below:

USE [BackupDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [SalesLT].[Orders]
AS
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
         OrderDate, ProductID, COUNT_BIG(*) AS COUNT
      FROM SalesLT.SalesOrderDetail AS od, SalesLT.SalesOrderHeader AS o
      WHERE od.SalesOrderID = o.SalesOrderID
      GROUP BY OrderDate, ProductID
GO

In the above code, the data type of the Revenue column is a number. If I want to change its data type to varchar, I can use the CAST function as:

USE [BackupDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [SalesLT].[Orders]
AS
SELECT CAST(SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS varchar) AS Revenue,
         OrderDate, ProductID, COUNT_BIG(*) AS COUNT
      FROM SalesLT.SalesOrderDetail AS od, SalesLT.SalesOrderHeader AS o
      WHERE od.SalesOrderID = o.SalesOrderID
      GROUP BY OrderDate, ProductID
GO

Hence, in this way you can modify the data type of a column in a view.

Read: SQL Server stored procedure vs function

Alter view name in SQL Server

To modify the name of a view in SQL Server, there are two methods that you can use:

1. Using SQL Server management studio
2. Using T-SQL query

However, while changing the name of a view in SQL Server, you have to be careful because if there is any object in the database that is dependent on this view may not function.

Therefore, before renaming a view, make sure to check which objects are dependent on it. Also, it is advisable to drop the view and create it again instead of renaming it. Now let us see how we can rename a view in SQL Server.

Using SQL Server management studio:

• In the object explorer window, naviagte to your view, right click on the view that you want to rename and click on Rename.
• Now specify the new name for the view and press enter. The view will be renamed. Look at the below image for reference:

Using T-SQL query:

To rename a view in SQL Server using the T-SQL query method, you can use the system stored procedure named sp_rename. The general syntax is:

EXEC sp_rename 
    @objname = <schema_name.view_name>,
    @newname = <view_name>;

There are two parameters that you have to provide to this stored procedure:

1. @objname: The original name of the view
2. @newname: The new name for the view

Make sure that you have to specify the schema name in the @objname parameter along with the view name. Also, you have to specify only the view name in the @newname parameter. For example:

USE BackupDatabase
GO
EXEC sp_rename 
    @objname = 'SalesLT.vOrders',
    @newname = 'Orders';

In the above T-SQL code, I have changed the name of my view from SalesLT.vOrders to SalesLT.Orders. But, I did not specify the schema name in the @newname parameter.

You will see a warning as shown in the above image. But it is just a warning and not an error. Therefore the view will be renamed.

Thus, you might have learned how you can rename a view in SQL Server.

Read: SQL Server INSERT INTO SELECT 

Create or Alter view in SQL Server

You can use the CREATE OR ALTER VIEW statement in SQL Server to alter a view if it already exists in the database, otherwise create a new view with the same set of queries in the view body.

This helps to avoid errors in two situations:

1. You are trying to alter a view that does not exist. In this case, a new view will be created with the same set of queries.
2. You are trying to create a view, but it already exists in the database. In this case, you will alter the already existing view in the database instead of creating a new one.

Let us see how you can use the CREATE OR ALTER VIEW statement in SQL Server. I have not created any view with the name SalesLT.vOrders. If I run the below T-SQL query the first time, a view will be created with the name SalesLT.vOrders.

USE [BackupDatabase]
GO
CREATE OR ALTER VIEW [SalesLT].[vOrders]
   WITH SCHEMABINDING
   AS  
      SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
         OrderDate, ProductID, COUNT_BIG(*) AS COUNT
      FROM SalesLT.SalesOrderDetail AS od, SalesLT.SalesOrderHeader AS o
      WHERE od.SalesOrderID = o.SalesOrderID
      GROUP BY OrderDate, ProductID;
GO

If I run this T-SQL query again, I will not get any error because the view will be altered this time as it already exists in the database.

Hence, in this way, you can use the CREATE OR ALTER VIEW statement in SQL Server to create or alter a view in the database.

Read: SQL Server DateTime vs Datetime2

If exists alter view in SQL Server

In this section, I will explain how you can use the IF EXISTS statement with the ALTER VIEW statement.

Sometimes, you may want to modify a view that you have already dropped. In that case, if you try to alter the view, you may face an error saying that the view does not exist.

In such cases, you may want to add a validation check for the existence of the view in the database. You can use the IF EXISTS statement for this. This statement will check if the view exists in the database or not. For example:

USE BackupDatabase
GO
IF EXISTS(SELECT 1 FROM sys.views WHERE name = 'vOrders')
BEGIN
EXECUTE('ALTER VIEW SalesLT.vOrders
AS
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
         OrderDate, ProductID, COUNT_BIG(*) AS COUNT
      FROM SalesLT.SalesOrderDetail AS od, SalesLT.SalesOrderHeader AS o
      WHERE od.SalesOrderID = o.SalesOrderID
      GROUP BY OrderDate, ProductID')
END

• Firstly, the IF EXISTS statement will check if there is a view named vOrders in the database.
• If the view already exists, the EXECUTE statement will execute the ALTER VIEW query to alter the table.

In this way, you can verify the existence of a view in the database before issuing the ALTER VIEW command.

Read: Types of Backup in SQL Server

Alter view with schemabinding in SQL Server

Creating a view with the SCHEMABINDING option means that you are binding the view with the underlying tables. This means you cannot alter your underlying tables in such a way that will affect the view definition.

This means, first you have to change the view definitions, only then you will be able to change the definition of the underlying table.

To alter an existing view and bind it with the underlying tables, you can specify the SCHEMABINDING option in the ALTER VIEW statement.

For example, in the above section, I have created a view named SalesLT.Orders. Now If I want to bind this view with its underlying tables, I will write the ALTER VIEW statement as:

USE [BackupDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [SalesLT].[Orders]
WITH SCHEMABINDING
AS
SELECT CAST(SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS varchar) AS Revenue,
         OrderDate, ProductID, COUNT_BIG(*) AS COUNT
      FROM SalesLT.SalesOrderDetail AS od, SalesLT.SalesOrderHeader AS o
      WHERE od.SalesOrderID = o.SalesOrderID
      GROUP BY OrderDate, ProductID
GO

If the statement is executed successfully the view will be bound to the underlying tables.

Note:

If you want to alter a table that is bind to view using the SCHEMABINDING option, you have to drop the table and create it again. You cannot disable the SCHEMABINDING option of the view.

You may also like to read the following SQL Server tutorials.

• SQL Server logical operators and example
• How to use union in view SQL Server
• How to see view definition in SQL Server
• Pass parameters to view in SQL Server
• How to test stored procedure in SQL Server

Thus, you might have learned how to alter a view in various ways in SQL Server. And here is the list of topics that we covered in this tutorial.

• Can we alter view in SQL Server?
• Alter view in SQL Server management studio
• Alter view add column SQL Server
• Alter view column data type in SQL Server
• Alter view name in SQL Server
• Alter view permission in SQL Server
• Create or Alter view in SQL Server
• If exists alter view in SQL Server
• Alter view with schemabinding in SQL Server