Modify views
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
After you define a view, you can modify its definition in the SQL Server Database Engine without dropping and re-creating the view by using SQL Server Management Studio or Transact-SQL.
Limitations and restrictions
Modifying a view doesn't affect any dependent objects, such as stored procedures or triggers, unless the definition of the view changes in such a way that the dependent object is no longer valid.
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.
Use SQL Server Management Studio
In Object Explorer, select the plus sign next to the database where your view is located and then select the plus sign next to the Views folder.
Right-click on the view you wish to modify and select Design.
In the diagram pane of the query designer, make changes to the view in one or more of the following ways:
Select or clear the check boxes of any elements you wish to add or remove.
Right-click within the diagram pane, select Add Table..., and then select the additional columns you want to add to the view from the Add Table dialog box.
Right-click the title bar of the table you wish to remove and select Remove.
On the File menu, select Save view name.
Use Transact-SQL
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute. The example first creates a view and then modifies the view by using ALTER VIEW. A WHERE clause is added to the view definition.
USE AdventureWorks2022; GO -- Create a view CREATE VIEW HumanResources.EmployeeHireDate AS SELECT p.FirstName, p.LastName, e.HireDate FROM HumanResources.Employee AS e INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID; -- Modify the view by adding a WHERE clause to limit the rows returned ALTER VIEW HumanResources.EmployeeHireDate AS SELECT p.FirstName, p.LastName, e.HireDate FROM HumanResources.Employee AS e INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID WHERE HireDate < CONVERT(DATETIME, '20020101', 101); GO
Next steps
Feedback
https://aka.ms/ContentUserFeedback.
În curând: Pe parcursul anului 2024, vom elimina treptat Probleme legate de GitHub ca mecanism de feedback pentru conținut și îl vom înlocui cu un nou sistem de feedback. Pentru mai multe informații, consultați:Trimiteți și vizualizați feedback pentru