Start Updating view

Updating view

This simplifies access to the data because the underlying query does not have to be written on the client and then submitted each time a report is run. Although you can set column-level security in SQL Server, it is tricky to maintain because it's pretty well buried in the table properties.

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

As far as the user is concerned, the view looks just like a table.

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.

Views have never in the past been able to contain parameters; however, as shown later in the chapter, user-defined functions can now be used like views, effectively allowing you to create parameterized views that return different results depending on the parameter values that get passed in each time the function is run.

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.

The primary use of views is to present data to users of an application.

That means they should be updatable, just as "real" or base tables are.

To purchase books, visit Amazon or your favorite retailer. See our FAQ or contact customer service: 1-800-889-8969 / [email protected]View/Submit Errata Views are virtual tables.

Similar performance benefits can be gained with SQL Server by basing your reports on views. A simple solution to the issue of implementing row-level and column-level security is to revoke all permissions to the underlying tables.

For example, the following view selects a customer's total orders and contains joins between the Customers table, the Orders table, and the Order Details table. Instead, define views with only selected columns to implement column-level security and define views with a clause to implement row-level security.

The following Transact-SQL statement selects all the data for the view shown in Listing 9.1, sorting the result set on the Company Name column: statement wouldn't have to change. Company Name Views are created inside individual databases, however, views can reference tables and views in other databases or even on other servers (if the view is defined using distributed queries).