SQL View 的使用语法与原则

鸬鹚洲

1.
View只是存储下来的sql 语句
Views are nothing but saved SQL statements, and are sometimes referred as “Virtual Tables”.
Keep in mind that Views cannot store data (except for Indexed Views); rather they only refer to data present in tables.
2.
create a view
USE Northwind // 使用Northwind这个数据库
GO
CREATE VIEW vwSample
As
SELECT CustomerID, CompanyName, ContactName FROM CUSTOMERS
GO
use a view
SELECT * from vwSample

drop a view
DROP VIEW vwSample
3.
Creating Views with the SCHEMABIND Option
使用了这个选项之后，view里面引用到的表的shema就不能被改变了。
Creating a view with the SCHEMABINDING option locks the tables being referred by the view and prevents any changes that may change the table schema.
Notice two important points while creating a view with SCHEMABINDING OPTION:
The objects should be referred to by their owner names [two part name].
SELECT * is not permitted.
Here’s an example for a view with the SCHEMABIND OPTION:
CREATE VIEW vwSample
With SCHEMABINDING
As
SELECT
CustomerID,
CompanyName,
ContactName
FROM DBO.CUSTOMERS -- Two part name [ownername.objectname]
GO
4.
Creating Views with the ENCRYPTION Option （一般情况下不要使用这个选项）
This option encrypts the definition of the view. Users will not be able to see the definition of the View after it is created.
USE NORTHWIND
GO
CREATE VIEW vwSample
With ENCRYPTION
As
SELECT
CustomerID,
CompanyName,
ContactName
FROM DBO.CUSTOMERS
GO
SELECT *
FROM SYSCOMMENTS
WHERE ID FROM SYSOBJECTS WHERE XTYPE = ‘V’ AND
The view definition will be stored in an encrypted format in the system table named ‘syscomments’.
5.
Indexed Views
给一个view创建index
SQL SERVER 2000 allows an index to be created on a View. Wow! Previous versions of SQL SERVER will not allow you to do this. But one important point to be noted here is that the first index on the View should be a UNIQUE CLUSTERED INDEX only. SQL SERVER 2000 will not allow you to create any other INDEX unless you have an UNIQUE CLUSTERED INDEX defined on the view.
Let’s check out a sample example for an Indexed View:
CREATE VIEW vwSample
As
SELECT
CustomerID,
CompanyName,
ContactName
FROM DBO.CUSTOMERS
GO
CREATE UNIQUE CLUSTERED INDEX indClustered
ON NORTHWIND.DBO.VWSAMPLE (CUSTOMERID)
GO
The above statement will create a unique clustered index on the View.
6.
Almost any SQL that can be issued natively can be coded into a view; there are exceptions, however. For example, the UNION operator can not be used in a view and you cannot create a trigger on a view.
view里不能进行union操作
7.
The text of any view can be retrieved from the SQL Server system catalog using the system procedure sp_helptext (unless the view was created specifying WITH ENCRYPTION). For example, this statement:
使用命令来查看view的定义（也可以用这个命令来查看stored procedure的定义）
sp_helptext Sample_view
Might return the following output:
Text
CREATE VIEW Sample_View
AS SELECT title, au_fname, au_lname
FROM titles, titleauthor, authors
WHERE titles.title_id=titleauthor.title_id
AND authors.author_id=titleauthor.author_id
It is also possible to rename a view using the system procedure sp_rename.
8. 应该在确定要用的时候才去创建一个view。
Views should be created only when they achieve a specific, reasonable goal. Each view should have a specific application or business requirement that it fulfills before it is created. That requirement should be documented somewhere, preferably in a data dictionary or repository.
There are seven primary uses for which views excel. These are:
1. Security: to provide row and column level security
note: view里可以调用USER_NAME()来获得当前登录用户的信息，从而每个登录的用户都回看到不同的view
2. Efficient Access: to ensure efficient access paths
The use of proper join criteria and predicates on indexed columns can be coded into the view.
创建view的时候要特别留意sql语句的效率。
3. Complexity: to mask complexity from the user
4. Derived Data: to ensure proper data derivation
view里面可以包含计算出来的column，方便使用。
5. Domain Support: to provide domain support
A domain basically identifies the valid range of values that a column can contain.
Some of the functionality of domains can be implemented using views and the WITH CHECK OPTION clause.
6. Column Renaming:  to rename columns, and
7. Single Solution View: to provide solutions which can not be accomplished without views
The final view usage situation might actually be the most practical usage for views—when views are the only solution!
9.
do not needlessly create SQL Server objects that are not necessary.
使用view的代价
In terms of views, for every unnecessary view that is created SQL Server will insert rows into the following system catalog tables: syscolumns, syscomments, sysdepends, sysobjects, sysprocedures, and sysprotects. If uncontrolled view creation is permitted, disk usage will increase, I/O problems can occur, and inefficient catalog organization may result.
10. View Naming convention
" Therefore, it stands to reason that views should utilize the same naming conventions as are used for tables."
但是对于后台开发人员，对view的命名里加入标识，可能会有好处。
11.
Always Specify Column Names
When creating views SQL Server provides the option of specifying new column names for the view or defaulting to the same column names as the underlying base table(s). It is always advisable to explicitly specify view column names instead of allowing them to default,

Reference
介绍view的基本语法
http://www.sql-server-performance.com/nn_views.asp
(好文章，推荐！)使用view的指导原则(什么时候应该使用view，一些经验，命名等7)
Using Views in Microsoft SQL Server, By Craig S. Mullins
http://www.craigsmullins.com/cnr_0299b.htm

//转载之http://www.cnblogs.com/EricWang/archive/2008/07/09/1238744.html