The view can be seen as a virtual table or a storage query. The data that can be accessed can be stored in the database as a unique object. The SELECT statement is stored in the database. The result set of SELECT statements makes up the virtual table returned by the view. The user can use the virtual table by reference the view name in the Transact-SQL statement with the reference list. Use views to achieve the following or all of the features:
Limit the user on a particular row in the table. For example, only the employee is allowed to see the work that records its work within the work tracking table. Limit the user on a particular column. For example, for employees who are not responsible for handling sagregates, only they will allow them to see the name columns in the employee table, office column, work telephone column, and department columns, and cannot see any columns containing wage information or personal information. Use the columns in multiple tables to make them look like a table. The aggregation information is not provided. For example, display a sum of a column, or a maximum and minimum of the columns.
Create a view by defining the SELECT statement to retrieve the data displayed in the view. The data table referenced by the SELECT statement is called the base table of the view. In the following example, the TitleView in the PUBS database is a view that selects data in three base tables to display virtual tables that contain common data:
Create View TitleView
AS
SELECT TITLE, AU_ORD, AU_LNAME, PRICE, YTD_SALES, PUB_ID
From authors as a
Join Titleauthor as Ta On (a.au_id = ta.au_id)
Join Titles As TON (t.title_id = ta.title_id)
Thereafter, TitleView can be referenced in the statement with the method used by the reference list.
SELECT *
From titleview
A view can reference another view. For example, TitleView displayed is useful to managers, but the company usually only publishes the financial figures of this year's deadline in the quarterly or annual financial statements. A view can be created in which all TitleView columns except AU_ORD and YTD_SALES are included. With this new view, customers can get lists of books that have been listed without seeing financial information:
Create View Cust_TitleView
AS
SELECT TITLE, AU_LNAME, PRICE, PUB_ID
From titleview
A view can be used to partition data between multiple databases or Microsoft® SQL ServerTM 2000 instances. Partitioned views can be used to distribute database processing over the entire server group. The server group has the same performance advantages as the server aggregates and can be used to support the maximum WEB site or company data center processing requirements. The original table is subdivided into multiple member tables, and each member table contains the line set of original tables. Each member table can be placed in a database of different servers. Each server can also get a partition view. The partition view uses the Transact-SQL UNION operator that combines the results selected on all member tables as a single result set, which is exactly the same as the entire original table. For example, a table partition is performed between three servers. Define the following partition view on the first server:
Create View PartitionededView AS
SELECT *
From mydatabase.dbo.PartitionTable1
Union all
SELECT *
From server2.mydatabase.dbo.PartitionTable2
Union all
SELECT *
From server3.mydatabase.dbo.PartitionTable3
A similar partition view is defined on other two servers. With these three views, any reference-SQL statement on the three servers will see the same behavior as the original table. It seems that there is a copy of the original table on each server, and in fact each table has only one member table and partition view. For more information, see View Usage. As long as the modifications you do only affect one of the base tables referenced by the view, you can update the views within all SQL Server versions (you can perform UPDATE, DELETE, or INSERT statements).
- Increase the price for publisher '0736' by 10%.
Update TitleView
Set price = price * 1.10
WHERE PUB_ID = '0736'
Go
SQL Server 2000 supports more complex INSERT, UPDATE, and DELETE statements that can reference views. The INSTEAD OF trip can be defined on the view to specify individual updates that must be performed on the base table to support the INSERT, UPDATE, or DELETE statements. In addition, the partition view also supports the multiple member tables referenced by Insert, UDPATE, and DELETE statements.
The indexing view is the function of SQL Server 2000 that significantly increases the performance of complex view types, which are usually appearing in a data warehouse or other decision support system.
The result set of the view is usually not saved in the database, so the view is also called a virtual table. The result of the view is dynamically generated in the logic of statement and is generated during runtime. For more information, see the view parsing.
Complex queries (queries such as decision support systems) can reference a large number of rows in the base table and concentrating a large amount of information in relatively simple polymerization, such as sum or average. SQL Server 2000 supports creation of aggregated indexs on the view that performs such complex queries. When executing the CREATE INDEX statement, the result set of view Select is permanently stored in the database. SQL statements will now be referred to by reference, the response time will be significantly shortened. Modifications to basic data will be automatically reflected in the view.
The SQL Server 2000 CREATE VIEW statement supports the schemaBinding option to prevent the view from which the view references will change in the case where the view is not adjusted. Schemabinding must be specified for any view of any creation index.
