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. 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.To restrict the rows in a view, you use the same kinds of criteria in a clause. This happens when the view and the underlying table do not have the same owner.This can be implemented using the Once the view is created, its definition will be unreadable, as shown in Figure 9.2.Therefore, make sure that you save your view definition in a script file in case you ever need to modify it. 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.
Now let’s create a trigger on ‘new_employee’ table so that whenever we are updating any employee record in the ‘new_employee’ table the same record also gets stored in ‘new_employee_duplicate’ 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. Then grant appropriate permissions on the views for the users and roles you want to be able to access the data.Views can also be used to hide the real column names or underlying schema of your tables.Unlike stored procedures (which also support parameters), these parameterized functions can be updateable, as you'll see later in this chapter.The SQL-92 standard also mandates that views cannot be sorted with an values with a view; to support that, it also supports sorting.The triggers in views are covered later in this chapter.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.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: One of the most powerful uses of views is for reporting. 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.A view can be used to wrap up complex queries containing subqueries, outer joins, and aggregation. 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.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.