ALTER VIEW (T-SQL)

Alters a previously created view (created by executing CREATE VIEW) without affecting dependent stored procedures or triggers and without changing permissions. For more information about the parameters used in the ALTER VIEW statement, see CREATE VIEW.

Syntax

ALTER VIEW view_name [(column [,...n])]
[WITH ENCRYPTION]
AS
    select_statement
[WITH CHECK OPTION]

Arguments

view_name

Is the view to change.

column

Is the name of one or more columns, separated by commas, to be part of the given 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 title_id column in a CREATE VIEW statement, an ALTER VIEW statement can rename the title_id column (for example, to qty) and still have the permissions that are associated with the view using title_id.

---

n

Is a placeholder indicating the column can be repeated n number of times.

ENCRYPTION

Encrypts the syscomments entries that contain the text of the CREATE VIEW statement.

AS

Are the actions the view is to take.

select_statement

Is the SELECT statement that defines the view.

CHECK OPTION

Forces all data modification statements executed against the view to adhere to the criteria set within the select_statement defining the view.

Remarks

For more information about ALTER VIEW, see Remarks in CREATE VIEW.

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

If a view currently in use is modified by using ALTER VIEW, Microsoft® SQL Server™ takes an exclusive schema lock on the view. When the lock is granted (and there are no active users of the view), SQL Server deletes all copies of the view from the procedure cache. Existing plans referencing the view are still in the cache, but will be automatically recompiled the next time they are invoked.

Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user’s update request unambiguously to updates in the base tables referenced in the view’s definition.

Permissions

ALTER VIEW permissions default to members of the db_owner and db_ddladmin fixed database roles, and to the view owner. These permissions are not transferable.

Examples

A. Alter a view

This example creates a view that contains all authors called All_authors. Permissions are granted to the view, but requirements are changed to select all authors who are from Utah. Then, ALTER VIEW is used to replace the view.

-- Create a view from the authors table that contains all authors.

CREATE VIEW All_authors (au_fname, au_lname, address, city, zip)

AS

SELECT au_fname, au_lname, address, city, zip

FROM pubs..authors

GO

-- Here, grant SELECT permissions on the view to public.

GRANT SELECT ON All_authors TO public

GO

-- The view needs to be changed to include all authors

-- from Utah.

-- If ALTER VIEW is not used but instead the view is dropped and

-- re-created, the above GRANT statement and any other statements

-- dealing with permissions that pertain to this view

-- must be redone.

ALTER VIEW All_authors (au_fname, au_lname, address, city, zip)

AS

SELECT au_fname, au_lname, address, city, zip

FROM pubs..authors

WHERE state = 'UT'

GO

B. Use @@ROWCOUNT function in a view

This example uses the @@ROWCOUNT function as part of the view definition.

USE pubs

GO

CREATE VIEW yourview

AS

    SELECT title_id, title, mycount = @@ROWCOUNT, ytd_sales

    FROM titles

GO

SELECT *

FROM yourview

GO

-- Here, the view is altered.

USE pubs

GO

ALTER VIEW yourview

AS

    SELECT title, mycount = @@ ROWCOUNT, ytd_sales

    FROM titles

    WHERE type = 'mod_cook'

GO

SELECT *

FROM yourview

GO

See Also