Edit

Share via

ALTER VIEW (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Modifies a previously created view. This includes an indexed view. ALTER VIEW does not affect dependent stored procedures or triggers and does not change permissions.

Transact-SQL syntax conventions

Syntax

ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ]   
[ WITH <view_attribute> [ ,...n ] ]   
AS select_statement   
[ WITH CHECK OPTION ] [ ; ]  
  
<view_attribute> ::=   
{   
    [ ENCRYPTION ]  
    [ SCHEMABINDING ]  
    [ VIEW_METADATA ]       
}   

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse and Microsoft Fabric
  
ALTER VIEW [ schema_name . ] view_name [  ( column_name [ ,...n ] ) ]   
AS <select_statement>   
[;]

Arguments

schema_name
Is the name of the schema to which the view belongs.

view_name
Is the view to change.

column
Is the name of one or more columns, separated by commas, that are to be part of the specified view.

Important

Column permissions are maintained only when columns have the same name before and after ALTER VIEW is performed.

Note

In the columns for the view, the permissions for a column name apply across a CREATE VIEW or ALTER VIEW statement, regardless of the source of the underlying data. For example, if permissions are granted on the SalesOrderID column in a CREATE VIEW statement, an ALTER VIEW statement can rename the SalesOrderID column, such as to OrderRef, and still have the permissions associated with the view using SalesOrderID.

ENCRYPTION
Applies to: SQL Server 2008 (10.0.x) and later and Azure SQL Database.

Encrypts the entries in sys.syscomments that contain the text of the ALTER VIEW statement. WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.

SCHEMABINDING
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped, unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail if these statements affect the view definition.

VIEW_METADATA
Specifies that the instance of SQL Server will return to the DB-Library, ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view. Browse-mode metadata is additional metadata that the instance of Database Engine returns to the client-side DB-Library, ODBC, and OLE DB APIs. This metadata enables the client-side APIs to implement updatable client-side cursors. Browse-mode metadata includes information about the base table that the columns in the result set belong to.

For views created with VIEW_METADATA, the browse-mode metadata returns the view name and not the base table names when it describes columns from the view in the result set.

When a view is created by using WITH VIEW_METADATA, all its columns, except a timestamp column, are updatable if the view has INSERT or UPDATE INSTEAD OF triggers. For more information, see the Remarks section in CREATE VIEW (Transact-SQL).

AS
Are the actions the view is to take.

select_statement
Is the SELECT statement that defines the view.

WITH CHECK OPTION
Forces all data modification statements that are executed against the view to follow the criteria set within select_statement.

Remarks

For more information about ALTER VIEW, see Remarks in CREATE VIEW (Transact-SQL).

Note

If the previous view definition was created by using WITH ENCRYPTION or CHECK OPTION, these options are enabled only if they are included in ALTER VIEW.

If a view currently used is modified by using ALTER VIEW, the Database Engine takes an exclusive schema lock on the view. When the lock is granted, and there are no active users of the view, the Database Engine deletes all copies of the view from the procedure cache. Existing plans referencing the view remain in the cache but are recompiled when invoked.

ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view.

Permissions

To execute ALTER VIEW, at a minimum, ALTER permission on OBJECT is required.

Examples

The following example creates a view that contains all employees and their hire dates called EmployeeHireDate. Permissions are granted to the view, but requirements are changed to select employees whose hire dates fall before a certain date. Then, ALTER VIEW is used to replace the view.

USE AdventureWorks2022;  
GO  
CREATE VIEW HumanResources.EmployeeHireDate  
AS  
SELECT p.FirstName, p.LastName, e.HireDate  
FROM HumanResources.Employee AS e JOIN Person.Person AS  p  
ON e.BusinessEntityID = p.BusinessEntityID ;  
GO

The view must be changed to include only the employees that were hired before 2002. If ALTER VIEW is not used, but instead the view is dropped and re-created, the previously used GRANT statement and any other statements that deal with permissions pertaining to this view must be re-entered.

ALTER VIEW HumanResources.EmployeeHireDate  
AS  
SELECT p.FirstName, p.LastName, e.HireDate  
FROM HumanResources.Employee AS e JOIN Person.Person AS p  
ON e.BusinessEntityID = p.BusinessEntityID  
WHERE HireDate < CONVERT(DATETIME,'20020101',101) ;  
GO

See Also

CREATE TABLE (Transact-SQL)
CREATE VIEW (Transact-SQL)
DROP VIEW (Transact-SQL)
Create a Stored Procedure
SELECT (Transact-SQL)
EVENTDATA (Transact-SQL)
Make Schema Changes on Publication Databases