Skip to content

modify-views.md

Latest commit

 

History

History
103 lines (70 loc) · 4.48 KB

modify-views.md

File metadata and controls

103 lines (70 loc) · 4.48 KB
title Modify Views | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql
ms.prod_service table-view-index, sql-database, sql-data-warehouse, pdw
ms.reviewer
ms.technology table-view-index
ms.topic conceptual
helpviewer_keywords
views [SQL Server], renaming
views [SQL Server], modifying
modifying views
renaming views
ms.assetid 2d3c14dc-43e5-4324-b8fb-f2692d330b16
author stevestein
ms.author sstein
monikerRange >=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Modify Views

[!INCLUDEtsql-appliesto-ss2008-asdb-asdw-pdw-md] After you define a view, you can modify its definition in [!INCLUDEssCurrent] without dropping and re-creating the view by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

In This Topic

Before You Begin

Limitations and Restrictions

  • Modifying a view does not 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 [!INCLUDEssDE] takes an exclusive schema lock on the view. When the lock is granted, and there are no active users of the view, the [!INCLUDEssDE] 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.

Security

Permissions

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

Using SQL Server Management Studio

To modify a view

  1. In Object Explorer, click the plus sign next to the database where your view is located and then click the plus sign next to the Views folder.

  2. Right-click on the view you wish to modify and select Design.

  3. In the diagram pane of the query designer, make changes to the view in one or more of the following ways:

    1. Select or clear the check boxes of any elements you wish to add or remove.

    2. 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.

    3. Right-click the title bar of the table you wish to remove and select Remove.

  4. On the File menu, click Saveview name.

Using Transact-SQL

To modify a view

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click 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 AdventureWorks2012 ;  
GO  
-- Create a view.  
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 ;   

-- 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 JOIN Person.Person AS  p  
ON e.BusinessEntityID = p.BusinessEntityID  
WHERE HireDate < CONVERT(DATETIME,'20020101',101) ;   
GO

For more information, see ALTER VIEW (Transact-SQL).