A view is simply a query stored as an object in the database. In essence a view can be thought of a virtual table, that in itself does not contain any data or information. A view can return the same data that we could from any SELECT statement. Views are implemented as stored SELECT statements that have virtually no overhead because they don't actually store data. However, a view is not a tool for processing data using T-SQL commands, like a stored procedure is. A view is only able to hold one query at a time. Therefore, a view is more of a query than a stored procedure. Just as with a stored procedure or a query within a Query Editor pane, you can include tables from databases that are running on different servers. Providing the user ID has the necessary security credentials, it is possible to include tables from several databases.
Views have been available throughout the history of Microsoft SQL Server. When working on a fully normalised database and querying the data, there is often a requirement to write complex joins, aggregates, functions and plethora of other T_SQL functions to produce the results. To mask this complexity, this statement can be created as a view, and then future queries can be preformed against the view.
Products like Oracle have come out with the concept of a Materialized View that give an additional performance. However, using views that return very large result sets can lead to poor performance, as the result set is not indexed and the entire result must be table scanned if the view is used in a join or a subquery. A T-SQL boost to this problem is by being able to have indexes built on a view. So in the continuing evolution of the SQL Server product line and in response to Oracle's Materialized View, Microsoft SQL Server 2000 has a new feature called the View Index. View Indexes give the product the capability to define an index on a view. Additionally, SQL Server View Indexes are dynamic in that changes to the data in the base tables are automatically reflected in the indexed view. Also the SQL Server query optimizer will try to use an indexed view even if the view is not referenced in the from clause of a T-SQL command. These features are not available in Oracle's Materialized Views.
WHERE clause. If we use USER_ID(), we can even find out who is using the view, and return only data that is relevant for this user. Furthermore, a view can join several tables, in several databases on several servers, but all the users use is the view's name. Simple, but powerful! UNION operator. For example if we had customers in Europe and United States, we could have one server for Europe, and one for United States, with tables that are identical, and then merge all data in a partitioned view. Choose an intuitive name that defines your view, such as dbo.Customer_Order_View, the owner name is optional, but it should be specified to avoid broken ownership chains (assuming the referenced DB objects are owned by DBO). When a view is created, the existence of objects referenced is verified. SELECT permission on the object are required if the owner differs from that of the view. If the view references derived columns, or if columns from multiple tables have the same name, column names must be specified. Column names can be aliased in order to make them user friendly.
select statement to make sure it returns desired results. It may also be a good time to test the performance of the query. INTO keyword or the COMPUTE/COMPUTE BY clauses ORDER BY clause can only be specified in conjunction with the TOP keyword. create view statement does not allow you to specify a database name. Views can be created in a variety of ways depending on which version of SQL server you are using:
CREATE VIEW owner.viewname [(Column[,n])]
select statement
[WITH CHECK OPTION]
CREATE VIEW uk_employee_view
SELECT * FROM employee WHERE country = 'UK'
WITH CHECK OPTION
If a view has been defined with the WITH CHECK OPTION, any modification to the underlying data can result in that data no longer being accessible through the view. This forces all data modification statements executed against the view to adhere to the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION guarantees that the data remains visible through the view after the modification has been committed.
You can create views through the enterprise manager by accessing the view designer. To access the view designer, right click on the views in the appropriate database and select new view. The view designer is actually based on the Query designer with one slight difference - when you select save, you are prompted for a name for the view and the query you have built is stored as the view definition.
After you are in the view designer, you can click on the Add Table icon (it's the one to the far right in the toolbar). Selecting the check boxes for the appropriate columns adds them to the select statement that will be saved as your view definition.
To change the properties of the view, i.e. adding the WITH CHECK option, select the properties icon or right click and select properties from the pop up menu.
In the SQL Management studio, navigate to the database you would like to create the view in. Navigate to the views node in the treeview and right click it. This will bring up a pop menu with New View as an option. From here, the procedure is very similar to the SQL 2000 one.
As a developer, one of your concerns when building a view is what happens when someone makes changes to the structure of an underlying table used in your view. This causes problems. I will show you how to get around this problem and secure the build of a view so that this sort of thing doesn't happen.
The most common situation where you will find views encrypted is when using a view
to return specific information from a database. You also don't wish anyone to see how that information was returned, for example not allowing contractor application developers see the structure of a source database. Encrypting the T-SQL code that makes up the view, which would mean that how the information was being returned would not be visible.
Just as tables can be indexed so can views, and the rules for choosing columns to make indexes are similar to those for a table, however there are some major requirements one needs to meet before one can index a view.
The first index to be created must be a unique clustered index, once this index has been created, additional non clustered indexes on the view can be created. An important note to bear in mind here is that if you drop the clustered index on the view, then all subsequent indexes on the view will be automatically dropped. Also if we drop a view, all the indexes will be dropped too. The view that the index is to be built on must only contain tables and not other views. The tables must all come from one database, and the view must also reside in that database and have been built with the SCHEMABINDING option.
The following options must be set to ON while creating an index on a view. These options need only be set to ON for that session and therefore would precede the CREATE INDEX statement.
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
/* */
SET NUMERIC_ROUNDABORT OFF
SET IGNORE_DUP_KEY OFF
To be continued...
| You must Sign In to use this message board. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
