Summary -

In this topic, we described about the Alter View with detailed example.

Note :- ALTER INDEX applies to SQL Server (all supported versions), Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, Parallel Data Warehouse.

In some cases, we my required to change the structure of the view due to either adding additional columns or hiding the unwanted columns from displaying to the users. For this purpose, we can use the ALTER VIEW. In ALTER VIEW statement, we can add new columns, or we can remove the columns without deleting the view.

By using ALTER VIEW statement, we can change the Structure of the view.

Syntax -

ALTER VIEW existing_view_name AS
SELECT column1, column2, …, columnN
FROM table_name
[WHERE condition]; 
  • existing_view_name - Specifies the name of the existing view.
  • column1, column2, …, columnN - Specifies the column names.
  • table_name - Specifies the base table name.
  • condition - Specifies the condition name.

Example –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

emp_id emp_name designation
001 Employee1 Director
002 Employee2 Director
003 Employee3 Manager
004 Employee4 Manager
005 Employee5 Analyst
006 Employee6 Analyst
007 Employee7 Clerk
008 Employee8 Salesman
009 Employee9 Salesman

Scenario – Change the existing view without adding new columns.

Requirement – Change the existing view employee_v by deleting designation column form it. The query was as follows -

ALTER VIEW employee_v AS SELECT emp_id, emp_name
FROM employee_details;  

By executing above query, we can change the employee_v view. The output was shown as below -

emp_id emp_name
001 Employee1
002 Employee2
003 Employee3
004 Employee4
005 Employee5
006 Employee6
007 Employee7
008 Employee8
009 Employee9

Scenario – Change the existing view with adding new columns.

Requirement – Change the existing view employee_v by dding a column manager_id column. The query was as follows -

ALTER VIEW employee_v AS SELECT emp_id, emp_name, manager_id
FROM employee; 

By executing above query, we can change the employee_v view by adding new column without deleting the existing view. The output was shown as below –

emp_id emp_name manager_id
001 Employee1
002 Employee2
003 Employee3 Employee1
004 Employee4 Employee2
005 Employee5 Employee3
006 Employee6 Employee3
007 Employee7 Employee3
008 Employee8 Employee4
009 Employee9 Employee4