SQL SERVER技術內幕之5 表運算式

標籤：

    表運算式是一種命名的查詢運算式，代表一個有效關係表。可以像其他表一樣，在資料處理語句中使用表運算式。SQL Server支援4種類型的表運算式:派生表(derived table)、通用資料表運算式(CTE,common table expression)、視圖以及內聯資料表值函式(inline TVF,inline table-valued function)。

    表運算式並不是物理上真實存在的什麼對象，它們是虛擬。對於表運算式的查詢在資料庫引擎內部都將轉換為對底層對象的查詢。使用表運算式的好處通常體現在代碼的邏輯方面，而不是效能方面。

    1.派生表

    派生表(也稱為表子查詢)是在外部查詢的FROM子句中定義的，派生表的存在範圍為定義它的外部查詢，只要外部查詢一結束，派生表也就不存在了。 定義派生表的查詢語句要寫在一對圓括弧內，後面跟著AS子句和派生表的名稱。例如，以下代碼定義了一個名為USACusts的派生表，它是一個返回所有美國客戶的查詢:

SELECT *FROM (SELECT custid, companyname      FROM Sales.Customers      WHERE country = N‘USA‘) AS USACusts;

    要有效地定義任何類型的表運算式，查詢語句必須滿足三個要求:

    (1).不保證有一定的順序。表運算式代表的是一個表，而關係表中的行是沒有固定順序的。ANSI SQL不允許在用於定義表表達表的查詢語句中有ORDER BY子句，T-SQL大體上遵守了這一限制，只有一個例外情況--當在語句中指定了TOP。在帶有TOP選項的查詢語句中，ORDER BY子句的邏輯目的只有一個:為TOP選項定義要篩選出哪些行。

    (2).所有的列必須有名稱。

    (3).所有的列名必須是唯一的。

    1.1 分配列別名

    (1)用內聯別名的形式對派生表進行查詢

SELECT orderyear, COUNT(DISTINCT custid) AS numcustsFROM (SELECT YEAR(orderdate) AS orderyear, custid      FROM Sales.Orders) AS DGROUP BY orderyear;

    (2)用外部命名格式對派生表進行查詢

SELECT orderyear, COUNT(DISTINCT custid) AS numcustsFROM (SELECT YEAR(orderdate), custid      FROM Sales.Orders) AS D(orderyear, custid)GROUP BY orderyear;

 

    2.通用資料表運算式(CTE)

    以下代碼定義了一個名為USACusts的CTE,它的內部查詢返回所有來自美國的客戶，外部查詢則選擇了CTE中的所有行，樣本如下:

WITH USACusts AS(  SELECT custid, companyname  FROM Sales.Customers  WHERE country = N‘USA‘)SELECT * FROM USACusts;

    2.1 分配列別名

    CTE也支援兩種格式的列別名命名方式--內聯格式和外部格式。對於內聯格式，要指定<expression> AS <column_alias>;對於外部格式，在CTE名稱後面的一對圓括弧中指定目標列的列表。樣本如下:

-- Inline column aliasingWITH C AS(  SELECT YEAR(orderdate) AS orderyear, custid  FROM Sales.Orders)-- External column aliasingWITH C(orderyear, custid) AS(  SELECT YEAR(orderdate), custid  FROM Sales.Orders)

    2.2 定義多個CTE

    從表面來看，派生表和CTE之間的區別可能只是語義方面。不過CTE具有幾個重要優勢。優勢之一就是:如果須要在一個CTE中引用另一個CTE,不須要像派生表那樣進行嵌套，相反，只要簡單地在同一WITH子句中定義多個CTE,並用逗號把它們分隔開。每個CTE可以引用在它前面定義的所有CTE，而外部查詢則可以引用所有CTE。

WITH C1 AS(  SELECT YEAR(orderdate) AS orderyear, custid  FROM Sales.Orders),C2 AS(  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts  FROM C1  GROUP BY orderyear)SELECT orderyear, numcustsFROM C2WHERE numcusts > 70

    2.3 CTE的多引用

    CTE是先定義再查詢，就外部查詢的FROM子句來說，CTE是已經存在的，因此，可以引用同一個CTE的多個執行個體。

    2.4 遞迴CTE

    定義一個遞迴CTE至少需要兩個查詢，第一個查詢稱為錨點成員(anchor member),第二個查詢稱為遞迴成員(recursive member)。錨點成員查詢只會被調用一次。遞迴成員是一個引用了CTE名稱的查詢。對CTE名稱的引用代表的是在一個執行序列中邏輯上的"前一個結果集"。第一次調 用遞迴成員時，"前一個結果集"代表由定位成員返回的結果集，之後每次調用遞迴成員時，對CTE名稱的引用代表對遞迴成員的前一次調用所返回的結果集。在 查詢返回的結果上，兩個成員查詢必須在列的個數和相應列的資料類型上保持相容。樣本如下所示:

WITH EmpsCTE AS(  SELECT empid, mgrid, firstname, lastname  FROM HR.Employees  WHERE empid = 2(錨點成員)    UNION ALL    SELECT C.empid, C.mgrid, C.firstname, C.lastname  FROM EmpsCTE AS P    JOIN HR.Employees AS C      ON C.mgrid = P.empid(遞迴成員))SELECT empid, mgrid, firstname, lastnameFROM EmpsCTE;

    錨點成員對HR.Employees表進行查詢，只返回僱員等於2的記錄行。第一次調用遞迴成員，返回僱員2的直屬員工，比如3和5。第二次調用遞迴成員，返回僱員3和5的直屬員工，以此類推，直到遞迴成員返回一個空的結果集。

 

    3.視圖

    到目前為止，我們討論了兩種類型的表運算式(派生表和CTE)，它們的作用範圍都非常有限，僅限於在單個語句的範圍內使用。只要包含這些表運算式的外部查詢完成操作，它們便會隨之消失。而視圖和內聯資料表值函式(inline TVF)是兩種可重用的表運算式，它們的定義儲存在一個資料庫物件中，一旦建立，就些對象就是資料庫的永久部分。樣本如下所示:

USE TSQLFundamentals2008;IF OBJECT_ID(‘Sales.USACusts‘) IS NOT NULL  DROP VIEW Sales.USACusts;GOCREATE VIEW Sales.USACustsASSELECT  custid, companyname, contactname, contacttitle, address,  city, region, postalcode, country, phone, faxFROM Sales.CustomersWHERE country = N‘USA‘;

    一般推薦在和視圖在關的應用上下文中應用避免使用SELECT *語句，列是在編譯視圖時進行枚舉的，新加的列可能不會自動添加到視圖中。如果在底層表中添加了列，而且在視圖中需要這些新加的列，則可以使用ALTER VIEW語句對視圖進行相應的修改。

    3.1 視圖選項

    (1)ENCRYPTION選項

    在建立或修改視圖、預存程序、觸發器用使用者定義函數時，都可以使用ENCRYPTION選項。如果指定ENCRYPTION選項，SQL Server在內部會對定義對象的文本進行混淆處理，普通使用者通過任何目錄對象都無法直接看到這種經過混淆處理的文本，只有特權使用者通過特殊手段才能訪問建立對象的文本。以下代碼可以返回對象的建立文本。

SELECT OBJECT_DEFINITION(OBJECT_ID(‘Sales.USACusts‘));GO

    但是當修改視圖定義，並指定ENCRYPTION選項時，視圖定義的文本則返回NULL

ALTER VIEW Sales.USACusts WITH ENCRYPTIONAS

 

    (2)SCHEMABINDING選項

    一旦指定了這個選項，被引用的對象就不能刪除，被引用的列也不能刪除或修改。

ALTER VIEW Sales.USACusts WITH SCHEMABINDING

 

    (3)CHECK OPTION

    選項的目的是為了防止通過視圖執行的資料修改與視圖中設定的過濾條件發生衝突，如果沒有設定CHECK OPTION選項，則可以通過USACusts視圖插入一條UK的客戶，這樣的更新會傳遞到底層的資料表中。但是，修改後的客戶將不能在視圖中顯示出來，因為它已經不再滿足視圖的查詢過濾條件。如果想防止這種與視圖的查詢過濾條件相衝突的修改，只須在定義視圖的查詢語句末尾加上WITH CHECK OPTION即可。

-- Add CHECK OPTION to the ViewALTER VIEW Sales.USACusts WITH SCHEMABINDINGASSELECT  custid, companyname, contactname, contacttitle, address,  city, region, postalcode, country, phone, faxFROM Sales.CustomersWHERE country = N‘USA‘WITH CHECK OPTION;GO

 

    4.內聯資料表值函式

    內聯資料表值函式是一種可重要的表運算式，能夠支援輸入參數，支援輸入參數以外，內聯資料表值函式在其他方面都與視圖類似。樣本如下所示:

-- Creating fn_GetCustOrders functionUSE TSQLFundamentals2008;IF OBJECT_ID(‘dbo.fn_GetCustOrders‘) IS NOT NULL  DROP FUNCTION dbo.fn_GetCustOrders;GOCREATE FUNCTION dbo.fn_GetCustOrders  (@cid AS INT) RETURNS TABLEASRETURN  SELECT orderid, custid, empid, orderdate, requireddate,    shippeddate, shipperid, freight, shipname, shipaddress, shipcity,    shipregion, shippostalcode, shipcountry  FROM Sales.Orders  WHERE custid = @cid;GO

-- Test FunctionSELECT orderid, custidFROM dbo.fn_GetCustOrders(1) AS CO;

   

    5.APPLY運算子

    APPLY運算子支援兩種形式:CROSS APPLY和OUTER APPLY。APPLY運算子對兩個輸入表進行操作，其中第二個可以是一個表運算式，將它們分別稱為左表和右表。CROSS APPLY實現了一個邏輯處理步驟：將右表運算式應用到左表中的每一行，再將結果集組合起來，產生一個統一的結果表。樣本如下所示：

    以下代碼使用CROSS APPLY返回每個客戶最新的三個訂單：

SELECT C.custid, A.orderid, A.orderdateFROM Sales.Customers AS C  CROSS APPLY    (SELECT TOP(3) orderid, empid, orderdate, requireddate      FROM Sales.Orders AS O     WHERE O.custid = C.custid     ORDER BY orderdate DESC, orderid DESC) AS A;

    如果右表運算式返回的是一個空集，CROSS APPLY則不會返回相應左邊的資料行。如果要在右表運算式返回空集時也照樣返回相應左表中的行，則可以用OUTER APPLY運算子代替CROSS APPLY。

   

    總結

    藉助表運算式可以簡化代碼，提高代碼的可維護性，還可以封裝查詢邏輯。當需要使用表運算式，而且不計劃重用它們的定義時，可以使用派生表或CTE。與派生表相比，CTE具有兩個優點:CTE不用像派生表那樣嵌套使用，此外，還可以引用同一CTE的多個執行個體，也派生表不能這麼用。

    當需要定義可重用的表運算式時，可以使用視圖和內聯資料表值函式。如果不須要支援輸入參數，則使用視圖，相反則使用內聯資料表值函式

SQL SERVER技術內幕之5 表運算式