本篇主要是對表表達式中視圖和内聯表值函數基礎的總結。
以前總是追求新東西，發現基礎才是最重要的，今年主要的目标是精通SQL查詢和SQL性能優化。
本系列【T-SQL基礎】主要是針對T-SQL基礎的總結。
【T-SQL基礎】01.單表查詢-幾道sql查詢題
【T-SQL基礎】02.聯接查詢
【T-SQL基礎】03.子查詢
【T-SQL基礎】04.表表達式-上篇
【T-SQL基礎】04.表表達式-下篇
【T-SQL基礎】05.集合運算
【T-SQL基礎】06.透視、逆透視、分組集
【T-SQL基礎】07.資料修改
【T-SQL基礎】08.事務和并發
【T-SQL基礎】09.可程式設計對象
----------------------------------------------------------
【T-SQL進階】01.好用的SQL TVP~~獨家贈送[增-删-改-查]的例子
【T-SQL性能調優】01.TempDB的使用和性能問題
【T-SQL性能調優】02.Transaction Log的使用和性能問題
【T-SQL性能調優】03.執行計劃
【T-SQL性能調優】04.死鎖分析
持續更新......歡迎關注我！
概述：
表表達式包含四種：
1.派生表
2.公用表表達式
3.視圖
4.内聯表值函數
本篇是表表達式的下篇，隻會講到視圖和内聯表值函數。
下面是表表達式的思維導圖：
表表達式：
1.一種命名的查詢表達式，代表一個有效的關系表。
2.可以像其他表一樣，在資料處理語句中使用表表達式。
3.在實體上不是真實存在的什麼對象，它們是虛拟的。對于表達式的查詢在資料庫引擎内部都将轉化為對底層對象的查詢。
為什麼使用表表達式：
1.使用表表達式的好處是邏輯方面，在性能上沒有提升。
2.通過子產品化的方法簡化問題的解決方案，規避語言上的某些限制。在外部查詢的任何字句中都可以引用在内部查詢的SELECT字句中配置設定的列别名。比如在SELECT字句中起的别名，不能在WHERE,group by等字句（邏輯順序位于SELECT字句之前的字句）中使用，通過表表達式可以解決這類問題。
在閱讀下面的章節時，我們可以先把環境準備好，以下的SQL腳本可以幫助大家建立資料庫，建立表，插入資料。
下載下傳腳本檔案：TSQLFundamentals2008.zip
一、視圖
1.視圖和派生表和CTE的差別和共同點
差別：
派生表和CTE不可重用：隻限于在單個語句的範圍内使用，隻要包含這些表表達式的外部查詢完成操作，它們就消失了。
視圖和内聯表值函數是可重用的：它們的定義存儲在一個資料對象中，一旦建立，這些對象就是資料庫的永久部分；隻有用删除語句顯示删除或用右鍵删除，它們才會從資料庫中移除。
共同點：
在很多方面，視圖和内聯表值函數的處理方式都類似于派生表和CTE。當查詢視圖和内聯表值函數時，SQL Server會先擴充表表達式的定義，再直接查詢底層對象。
2.文法
下面的例子定義了一個視圖，視圖名稱為Sales.USACusts，查詢所有來自美國的客戶。
USE TSQLFundamentals2008
IF OBJECT_ID('Sales.USACusts') IS NOT NULL
DROP VIEW Sales.USACusts;
GO
CREATE VIEW Sales.USACusts
AS
SELECT custid ,
companyname ,
contacttitle ,
address ,
city ,
region ,
postalcode ,
country ,
phone ,
fax
FROM Sales.Customers
WHERE country = N'USA' 定義好了視圖之後，在資料庫中重新整理視圖清單之後就會出現剛剛建立的視圖Sales.USACusts
然後，就可以像查詢資料庫中其他表一樣對視圖進行查詢：
SELECT custid ,
companyname
FROM sales.usacusts 3.視圖的權限
可以像其他資料庫對象一樣，對視圖的權限進行控制：如SELECT、INSERT、UPDATE、DELETE權限
4.避免使用SELECT * 語句
列是在編譯視圖時進行枚舉的，新加的列不會自動加到視圖中。以後對視圖中用到的表增加了幾列，這些列不會自動添加到視圖中。可以用sp_refreshview的存儲過程重新整理視圖的中繼資料，但是為了以後的維護，還是在視圖中顯示地需要的列名。如果在底層表中添加了列，而在視圖中需要這些新加的列，可以使用ALTER VIEW語句對視圖定義進行相應的修改。
5.建立視圖的要求：
必須要滿足之前介紹派生表時對表表達式提到的所有要求：
a.列必須有名稱
b.列必須唯一
c.不保證有一定的順序。在定義表表達式的查詢語句中不允許出現ORDER BY字句。因為關系表的行之間沒有順序。
6.加密選項ENCRYPTION
在建立視圖、存儲過程、觸發器及使用者定義函數時，都可以使用ENCRYPTION加密選項。如果指定ENCRYPTION選項，SQL Server在内部會對定義對象的文本資訊進行混淆（obfuscated）處理。普通使用者看不到該視圖的文本，隻有特權使用者通過特殊手段才能通路建立對象的文本。
在視圖定義的頭部，用WITH字句來指定ENCRYPTION選項，如下所示：
CREATE VIEW Sales.USACusts WITH ENCRYPTION 可以用下面的語句檢視視圖的文本：
SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts')) 結果如下：
也可以用sp_helptext檢視視圖的文本：
sp_helptext 'Sales.USACusts' 隻有在對安全要求較高的情況下才需要對視圖進行加密，一般情況不需要加密。
7.架構綁定選項SCHEMABINDING
視圖和使用者自定義函數支援SCHEMABINDING選項。一旦指定了這個選項，視圖引用的對象不能删除，被引用的列不能删除或修改。
在視圖定義的頭部，用WITH字句來指定SCHEMABINDING選項，如下所示：
CREATE VIEW Sales.USACusts WITH SCHEMABINDING 可以用下面的語句，更新Sales.USACusts視圖所引用的Sales.Customers對象的address列
ALTER TABLE Sales.Customers DROP COLUMN address 建議在建立視圖時，使用SCHEMABINDING選項。
如果使用SCHEMABINDING選項，必須滿足兩個技術要求：
a.必須在SELECT字句中顯示地列出列名
b.在引用對象時，必須使用帶有架構名稱修飾的完整對象名稱。
8.CHECK OPTION選項
CHECK OPTION選項的目的是為了防止通過視圖執行的資料修改與視圖中設定的過濾條件（假設在定義視圖的查詢中存在過濾條件）發生沖突。
假設想通過Sales.USACusts視圖往Sales.Customers表中插入資料，可以使用下面的語句：
INSERT INTO Sales.USACusts
( companyname ,
contactname ,
contacttitle ,
address ,
city ,
region ,
postalcode ,
country ,
phone ,
fax
)
VALUES ( 'A' ,
'A' ,
'A' ,
'A' ,
'London' ,
'A' ,
'A' ,
'UK' ,
'123' ,
'123'
) 然後查詢Sales.Customers表，如下所示：
SELECT custid,companyname,country
FROM Sales.Customers
WHERE companyname = 'A' 結果：
如果用視圖進行查詢，如下所示：
SELECT custid ,
companyname ,
country
FROM Sales.USACusts
WHERE companyname = 'A' 則得到的是一個空的結果集，因為視圖中的WHERE條件WHERE country = N'USA'隻篩選來自美國的客戶。
如果想防止這種與視圖的查詢過濾條件相沖突的修改，隻須在定義視圖的查詢語句末尾加上WITH CHECK OPTION即可：
ALTER VIEW [Sales].[USACusts]
AS
SELECT custid ,
companyname ,
contactname ,
contacttitle ,
address ,
city ,
region ,
postalcode ,
country ,
phone ,
fax
FROM Sales.Customers
WHERE country = N'USA'
WITH CHECK OPTION;
GO 再試下插入與視圖的過濾條件相沖突的記錄：
INSERT INTO Sales.USACusts
( companyname ,
contactname ,
contacttitle ,
address ,
city ,
region ,
postalcode ,
country ,
phone ,
fax
)
VALUES ( 'A' ,
'A' ,
'A' ,
'A' ,
'London' ,
'A' ,
'A' ,
'UK' ,
'123' ,
'123'
) 9.練習題：
（1）建立一個視圖，傳回每個雇員每年處理的總訂貨量：
期望結果：
本題考察視圖的建立
IF OBJECT_ID('Sales.VEmpOrders') IS NOT NULL
DROP VIEW Sales.VEmpOrders;
GO
CREATE VIEW Sales.VEmpOrders
AS
SELECT empid ,
YEAR(orderdate) AS orderyear ,
SUM(qty) AS qty
FROM Sales.Orders AS O
INNER JOIN Sales.OrderDetails AS D ON O.orderid = D.orderid
GROUP BY empid ,
YEAR(orderdate);
GO （2）寫一個對Sales.VEmpOrders表的查詢，傳回每個雇員每年處理過的連續總訂貨量
期望的輸出：
此題需要用到相關子查詢：
SELECT empid ,
orderyear ,
qty ,
( SELECT SUM(qty) AS runqty
FROM Sales.VEmpOrders AS EO2
WHERE EO1.empid = EO2.empid
AND EO1.orderyear <= EO2.orderyear
)
FROM Sales.VEmpOrders AS EO1
ORDER BY EO1.empid , 子查詢傳回訂單年份小于或等于外查詢目前行的訂單年份的所有行，并計算這些行的訂貨量之和。
二、内聯表值函數
1.什麼是内聯表值函數
一種可重用的表表達式，能夠支援輸入參數。除了支援輸入參數以外，内聯表值函數在其他方面都與視圖相似。
2.如何定義内聯表值函數
下面的例子建立了一個函數fn_GetCustOrders。這個内聯表值接收一個輸入客戶ID參數@cid，另外一個輸入參數訂單年份參數@orderdateyear，傳回客戶ID等于@cid的客戶下的所有訂單，且訂單的訂單年份等于@orderdateyear
IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL
DROP FUNCTION dbo.fn_GetCustOrders
GO
CREATE FUNCTION dbo.fn_GetCustOrders ( @cid AS INT ,@orderdateyear AS DATETIME)
RETURNS TABLE
AS RETURN
SELECT orderid ,
custid ,
empid ,
orderdate ,
requireddate ,
shippeddate ,
shipperid ,
freight ,
shipname ,
shipaddress ,
shipcity ,
shipregion ,
shippostalcode ,
shipcountry
FROM Sales.Orders
WHERE custid = @cid AND YEAR(orderdate) = YEAR(@orderdateyear)
Go 定義好了内聯表值函數之後，在資料庫中重新整理可程式設計性-函數-表值函數清單之後就會出現剛剛建立的函數fn_GetCustOrders
3.如何使用内聯表值函數
可以用内聯表值函數查詢出客戶id=1,訂單日期年份=2008的所有訂單：
SELECT orderid,custid,orderdate FROM fn_GetCustOrders(1,'2008') 内聯表值函數也可以用在聯接查詢中：
下面的例子是用内聯表值函數與HR.Employees表進行關聯，查詢出客戶id=1,訂單日期年份=2008的所有訂單，以及處理對應訂單的員工詳情：
SELECT orderid ,
custid ,
orderdate ,
empid ,
lastname ,
firstname ,
title
FROM fn_GetCustOrders(1, '2008')
INNER JOIN HR.Employees AS E ON dbo.fn_GetCustOrders.empid = E.empid 結果如下：
4.練習題
（1）建立一個内聯表值函數，其輸入參數為供應商ID(@supid AS INT)和要求的産品數量（@n AS INT）。該函數傳回給定供應商@supid提供的産品中，單價最高的@n個産品。
當執行以下查詢時：
SELECT * FROM fn_TopProducts(5,2) 本題可以分三個步驟：
1.寫一個查詢語句
SELECT TOP ( 1 )
productid ,
productname ,
unitprice
FROM Production.Products
WHERE supplierid = 1
ORDER BY unitprice DESC; 2.将參數替換進去：
SELECT TOP ( @n )
productid ,
productname ,
unitprice
FROM Production.Products
WHERE supplierid = @supid
ORDER BY unitprice DESC; 3.将這個查詢放到内聯表值函數中
IF OBJECT_ID('dbo.fn_TopProducts') IS NOT NULL
DROP FUNCTION dbo.fn_TopProducts
GO
CREATE FUNCTION dbo.fn_TopProducts ( @supid AS INT, @n AS INT )
RETURNS TABLE
AS RETURN
SELECT TOP ( @n )
productid ,
productname ,
unitprice
FROM Production.Products
WHERE supplierid = @supid
ORDER BY unitprice DESC;
Go 三、APPLY運算符
1.APPLY運算符
APPLY運算符是一個非标準标準運算符。APPLY運算符對兩個輸入進行操作，其中右邊的表可以是一個表表達式。
CROSS APPLY：把右邊表達式應用到左表中的每一行，再把結果集組合起來，生成一個統一的結果表。和交叉連接配接相似
OUTER APPLY：把右邊表達式應用到左表中的每一行，再把結果集組合起來，然後添加外部行。和左外聯接中增加外部行的那一步相似
2.練習題
（1）使用CROSS APPLY運算符和fn_TopProducts函數，為每個供應商傳回兩個價格最貴的産品。
涉及到的表：Production.Suppliers
使用CROSS APPLY運算符為每個供應商應用前一個fn_TopProducts函數。
SELECT supplierid ,
companyname ,
productid ,
productname ,
unitprice
FROM Production.Suppliers AS S
CROSS APPLY fn_TopProducts(S.supplierid, 2) AS P 參考資料：