筆記-Microsoft SQL Server 2008技術內幕：T-SQL語言基礎-05 表運算式

標籤：

一般來說，表運算式既不會對效能產生正面影響，也不會對效能產生負面影響。

注意下面這種代碼風格：

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

通用資料表運算式

通用資料表運算式（CTE，Common table expression）是用WITH子句定義的，一般格式為：

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

 和派生表一樣，一旦外部查詢完成，CTE的生命期就結束了。

CTE分配列別名的方式——內聯格式和外部格式，內聯格式：

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

 外部格式：

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

 定義多個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;

 與嵌套的派生表代碼相比，上面這種模組化的代碼大大提高了代碼的可讀性和可維護性。

視圖

建立一個視圖：

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‘;GO

 記住一點，在定義表運算式的查詢語句中不允許出現ORDER BY子句，因為關係表之間的行沒有順序。試圖建立一個有序視圖也是不合理的，SQL Server將會報錯。應該在使用視圖的外部查詢中使用ORDER BY子句。

內聯資料表值函式

以下代碼建立一個內聯資料表值函式：

USE 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

 使用這個函數：

SELECT orderid, custidFROM dbo.fn_GetCustOrders(1) AS CO;

總結

藉助表運算式可以簡化代碼，提高代碼的維護性，還可以封裝查詢邏輯。當需要使用表運算式時，如果是不計劃重用它們的定義，則使用派生表或者CTE；當需要定義可重用的表運算式時，可以使用視圖和內聯資料表值函式。

筆記-Microsoft SQL Server 2008技術內幕：T-SQL語言基礎-05 表運算式