It can take a while to understand exactly what views can and cannot do. 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.
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.
Unlike stored procedures which also support parameters , these parameterized functions can be updateable, as you'll see later in this chapter. Like tables in a relational database, the order of the records in a view is undefined.
However, Transact-SQL includes an extension to the language that allows you to select TOP values with a view; to support that, it also supports sorting. Another limitation of views that you may be familiar with is that a view cannot be indexed. In SQL Server , you can indeed create indexed views, as shown later in this chapter. AS wrapper that causes the statement to be saved as a view. 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.
Views can also reference functions and other views. Join Tables 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. 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.
Customize Data Tables can be customized and tailored to the needs of the users by way of views. 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. Views give you a mechanism for presenting the same data to different users in different ways.
Restricting Columns and Rows Columns and rows can be restricted, allowing users to focus on specific data that interests them. Unnecessary data is left out. This also helps network performance because less data is traveling over the wire to client applications.
Using a view to filter data on the server is much more efficient than fetching an entire table and then applying a filter on the client. To restrict the rows in a view, you use the same kinds of criteria in a WHERE clause that you are already familiar with. For example, the following view limits the result set by selecting only the employee first name, last name, and hire date columns from the Employees table, and it limits the rows returned to employees hired this year: A view can be used to wrap up complex queries containing subqueries, outer joins, and aggregation.
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. You may already have discovered that you can often greatly speed up Access reports by first saving the results of a complex record source query into a temporary table and then basing the report on that table rather than on the query. Similar performance benefits can be gained with SQL Server by basing your reports on views.
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. When a user selects data from the view, only the result set is passed over the network—all of the joins and aggregations are performed on the server, not on the client.
CompanyName Rules for Creating and Using Views 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. Here are some general guidelines for creating views: Names of views must follow the same rules for identifiers, discussed in Chapter 7, "Designing and Creating a SQL Server Database," and they must be unique for each owner in the database.
However, your best bet is to make sure that all objects are owned by dbo so that there are no breaks in the ownership chain. Later in this chapter, views, security, and ownership chains are discussed. Views can be built on other views and can be nested up to 32 levels deep. To create a view, you must be granted permission to do so by the database owner, and you must have appropriate permissions on any tables or views referenced in the view definition.
Here's a list of the things you can't do in a view: Rules, defaults, and triggers cannot be associated with views. Full-text indexes can't be defined on views. However, other types of indexes can be defined on views in SQL Server This feature is not available in SQL Server 7.
Views cannot be based on temporary tables. You cannot issue full-text queries against a view, although a view definition can include a full-text query if the query references a table that has been configured for full-text indexing.
You can't pass parameters to views, although you'll learn later in the chapter how user-defined functions in SQL Server enable you to skirt this restriction. Views and Security Views are frequently used as security mechanisms to prevent users from having direct access to tables while still allowing them to work with data.
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. As long as both the view and the Customers table are owned by dbo, the permissions granted on the view will take precedence over permissions removed from the Customers table.
Because views allow you to restrict the rows and columns that are available, using them as a security tool is very convenient. For example, you could create two views on an Employees table, one with a sensitive salary column included that only managers had permissions on, and a second view with that column omitted for everyone else.
Chain of Ownership Issues One reason it's recommended that the dbo own all objects in a database is that problems arise when the chain of ownership is broken. This happens when the view and the underlying table do not have the same owner. When the chain of ownership is broken, you need to grant users the same permissions on the underlying tables as you want them to have on the view, which nullifies the effectiveness of views as security mechanisms.
Another problem you'll encounter with Access projects ADPs is that if you want users to be able to update data using a view, you also have to grant them permissions on the underlying tables. Row-level security must be defined at runtime because it's impossible to know what values a table will contain until then.
A simple solution to the issue of implementing row-level and column-level security is to revoke all permissions to the underlying tables. Instead, define views with only selected columns to implement column-level security and define views with a WHERE clause to implement row-level security.
Then grant appropriate permissions on the views for the users and roles you want to be able to access the data. Hiding Underlying Table or Column Names Views can also be used to hide the real column names or underlying schema of your tables. Therefore, make sure that you save your view definition in a script file in case you ever need to modify it. The definition of an encrypted view.