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).
The information about where the data in a view comes from and how it is pulled together from various tables is all encapsulated inside the view. Here are some general guidelines for creating views: Views are frequently used as security mechanisms to prevent users from having direct access to tables while still allowing them to work with data.
Tables can be customized and tailored to the needs of the users by way of views. All permissions can be removed from the underlying tables, and as long as the owner of the table is the same as the owner of the view, the user will be able to interact with the data based on the permissions granted to the view.For example, you could create a view for a salesperson that only displays her own orders, or you might create a view for the manager, who is only interested in seeing the total sales for each salesperson. Figure 9.1 shows the Permissions dialog box for vw Customeraddress List.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.Note the use of the alias CAT for the CATEGORY table in the FROM clause.
The previous example showed an inner join specified in the FROM clause of an UPDATE statement.
Because of the restriction on the CATGROUP column, only one row qualifies for the update (although four rows qualify for the join).
update category set catid=100 from event where event.catid=category.catid and catgroup='Concerts'; select * from category where catid=100; catid | catgroup | catname | catdesc ------- ---------- --------- --------------------------------- 100 | Concerts | Pop | All rock and pop music concerts (1 row) The advantage to this approach is that the join criteria are clearly separated from any other criteria that qualify rows for the update.
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 can be used in place of tables in your Access front-end application, whether you're using an Access database or an Access project.