Updating a view in sql

Rated 4.33/5 based on 707 customer reviews

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 Sales Order ID column in a CREATE VIEW statement, an ALTER VIEW statement can rename the Sales Order ID column, such as to Order Ref, and still have the permissions associated with the view using Sales Order ID.They can be used to provide row- or column-level access to data, to wrap up complex joins, to perform complex aggregate queries, and to otherwise customize the display of data. In this chapter, learn how to define, create, and modify views, and how to perform index analysis and optimize performance in SQL Server. The biggest problem is that just when you get used to accepting a limitation, Microsoft comes up with an amazing, new feature that overcomes it!It's great to get the extra features, but it makes it harder to nail down exactly what you can and cannot do with views.Browse-mode metadata includes information about the base table that the columns in the result set belong to.

A user can query a single view instead of having to learn complex join syntax and understand the structure of your database.

Another limitation of views that you may be familiar with is that a view cannot be indexed. In SQL Server 2000, you can indeed create indexed views, as shown later in this chapter.

Views still, however, don't allow you to perform any of the other major SQL actions besides selecting—views can't contain syntax when defining a view.

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). If a view currently used is modified by using ALTER VIEW, the Database Engine takes an exclusive schema lock on the view.

Leave a Reply