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