您正在看的SQLserver教程是:SQL Server 2005 Beta 2 Transact-SQL 增強功能 2。
假設您希望呈現 ItemAttributes 表中的數據，該表具有與每個油畫項目（項目 5、6）相對應的行以及與每個屬性相對應的列。如果沒有 PIVOT 運算符，則必須編寫如下所示的查詢：
SELECT itemid, MAX(CASE WHEN attribute = 'artist' THEN value END) AS [artist], MAX(CASE WHEN attribute = 'name' THEN value END) AS [name], MAX(CASE WHEN attribute = 'type' THEN value END) AS [type], MAX(CASE WHEN attribute = 'height' THEN value END) AS [height], MAX(CASE WHEN attribute = 'width' THEN value END) AS [width] FROM ItemAttributes AS ATR WHERE itemid IN(5,6) GROUP BY itemid
以下為結果集：
itemid artist name type height width ------ ---------------- ---------------- ---------- ------ ------ 5 Claude Monet Field of PoppIEs Oil 19.625 25.625 6 Vincent Van Gogh The Starry Night Oil 28.750 36.250
PIVOT 運算符使您可以維護更簡短且更可讀的代碼以獲得相同的結果：
SELECT * FROM ItemAttributes AS ATR PIVOT ( MAX(value) FOR attribute IN([artist], [name], [type], [height], [width]) ) AS PVT WHERE itemid IN(5,6)
像大多數新功能一樣，對 PIVOT 運算符的理解來自於試驗和使用。PIVOT 語法中的某些元素是顯而易見的，並且只需要您弄清楚這些元素與不使用新運算符的查詢之間的關系。其他元素則是隱藏的。
您可能會發現下列術語能夠幫助您理解 PIVOT 運算符的語義：
table_expression
PIVOT 運算符所作用於的虛擬表（查詢中位於 FROM 子句和 PIVOT 運算符之間的部分）：在該示例中為 ItemAttributes AS ATR。
pivot_column
table_expression 中您希望將其值旋轉為結果列的列：在該示例中為 attribute。
column_list
pivot_column 中您希望將其呈現為結果列的值列表（在 IN 子句前面的括號中）。它們必須表示為合法的標識符：在該示例中為 [artist]、[name]、[type]、[height]、[width]。
aggregate_function
用於生成結果中的數據或列值的聚合函數：在該示例中為 MAX()。
value_column
table_expression 中的用作 aggregate_function 的參數的列：在該示例中為 value。
group_by_list
隱藏的部分 — table_expression 中除 pivot_column 和 value_column 以外所有用來對結果進行分組的列：在該示例中為 itemid。
select_list
SELECT 子句後面的列列表，可能包括 group_by_list 和 column_list 中的任何列。別名可以用來更改結果列的名稱：* 在該示例中，返回 group_by_list 和 column_list 中的所有列。
PIVOT 運算符為 group_by_list 中的每個唯一值返回一個行，就好像您的查詢帶有 GROUP BY 子句並且您指定了這些列一樣。請注意，group_by_list 是隱含的；它沒有在查詢中的任何位置顯式指定。它包含 table_expression 中除 pivot_column 和 value_column 以外的所有列。理解這一點可能是理解您用 PIVOT 運算符編寫的查詢按照它們本身的方式工作以及在某些情況下可能獲得錯誤的原因的關鍵。
可能的結果列包括 group_by_list 和 中的值。如果您指定星號 (*)，則查詢會返回這兩個列表。結果列的數據部分或結果列值是通過將 value_column 用作參數的 aggregate_function 計算的。
下面的用各種顏色突出顯示的代碼說明了使用 PIVOT 運算符的查詢中的不同元素：
SELECT * -- itemid, [artist], [name], [type], [height], [width] FROM ItemAttributes AS ATR PIVOT ( MAX(value) FOR attribute IN([artist], [name], [type], [height], [width]) ) AS PVT WHERE itemid IN(5,6)
以下代碼將不同的元素與不使用 PIVOT 運算符的查詢相關聯：
SELECT itemid, MAX(CASE WHEN attribute = 'artist' THEN value END) AS [artist], MAX(CASE WHEN attribute = 'name' THEN value END) AS [name], MAX(CASE WHEN attribute = 'type' THEN value END) AS [type], MAX(CASE WHEN attribute = 'height' THEN value END) AS [height], MAX(CASE WHEN attribute = 'width' THEN value END) AS [width] FROM ItemAttributes AS ATR WHERE itemid IN(5,6) GROUP BY itemid
請注意，您必須顯式指定 中的值。PIVOT 運算符沒有提供在靜態查詢中從 pivot_column 動態得到這些值的選項。您可以使用動態 SQL 自行構建查詢字符串以達到該目的。
將上一個 PIVOT 查詢向前推進一步，假設您希望為每個拍賣項目返回所有與油畫相關的屬性。您希望包括那些出現在 AuctionItems 中的屬性以及那些出現在 ItemAttributes 中的屬性。您可能嘗試以下查詢，它會返回錯誤：
SELECT * FROM AuctionItems AS ITM JOIN ItemAttri
您正在看的SQLserver教程是:SQL Server 2005 Beta 2 Transact-SQL 增強功能 2。butes AS ATR ON ITM.itemid = ATR.itemid PIVOT ( MAX(value) FOR attribute IN([artist], [name], [type], [height], [width]) ) AS PVT WHERE itemtype = 'Painting'
以下為錯誤消息：
.Net SqlClient Data Provider:Msg 8156, Level 16, State 1, Line 1 The column 'itemid' was specifIEd multiple times for 'PVT'.
請記住，PIVOT 作用於 table_expression，它是由該查詢中 FROM 子句和 PIVOT 子句之間的部分返回的虛擬表。在該查詢中，虛擬表包含 itemid 列的兩個實例 — 一個源自 AuctionItems，另一個源自 ItemAttributes。您可能會試探按如下方式修改該查詢，但是您仍將獲得錯誤：
SELECT ITM.itemid, itemtype, whenmade, initialprice, [artist], [name], [type], [height], [width] FROM AuctionItems AS ITM JOIN ItemAttributes AS ATR ON ITM.itemid = ATR.itemid PIVOT ( MAX(value) FOR attribute IN([artist], [name], [type], [height], [width]) ) AS PVT WHERE itemtype = 'Painting'
以下為錯誤消息：
.Net SqlClient Data Provider: Msg 8156, Level 16, State 1, Line 1 The column 'itemid' was specifIEd multiple times for 'PVT'. .Net SqlClIEnt Data Provider: Msg 107, Level 15, State 1, Line 1 The column prefix 'ITM' does not match with a table
name or alias name used in the query.正如前面提到的那樣，PIVOT 運算符作用於由 table_expression 返回的虛擬表，而不是作用於 select_list 中的列。select_list 在 PIVOT 運算符執行它的操作之後計算，並且只能引用 group_by_list 和 column_list。這就是在 select_list 中不再識別 ITM 別名的原因。如果您了解這一點，您就會意識到，應當向 PIVOT 提供一個只包含您希望施加作用的列的 table_expression。這包括分組列（只有 itemid 的一個實例，外加 itemtype、whenmade 和 initialprice）、樞軸列 (attribute) 和值列 (value)。您可以通過使用 CTE 或派生表做到這一點。以下是一個使用 CTE 的示例：
WITH PNT AS ( SELECT ITM.*, ATR.attribute, ATR.value FROM AuctionItems AS ITM JOIN ItemAttributes AS ATR ON ITM.itemid = ATR.itemid WHERE ITM.itemtype = 'Painting' ) SELECT * FROM PNT PIVOT ( MAX(value) FOR attribute IN([artist], [name], [type], [height], [width]) ) AS PVT以下為結果集：
itemid itemtype whenmade initialprice artist name type height width ------ -------- -------- ------------ ---------------- ---------------- ---- ------ ----- 5 Painting 1873 8000000.0000 Claude Monet Field of PoppIEs Oi歡迎光臨學網,收藏本篇文章 [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13] [14] [15] [16] [17] [18] [19] [20] [21]
l 19.62 25.62 6 Painting 1889 8000000.0000 Vincent Van Gogh The Starry Night Oil 28.75 36.25
以下是一個使用派生表的示例：
SELECT * FROM (SELECT ITM.*, ATR.attribute, ATR.value FROM AuctionItems AS ITM JOIN ItemAttributes AS ATR ON ITM.itemid = ATR.itemid WHERE ITM.itemtype = 'Painting') AS PNT PIVOT ( MAX(value) FOR attribute IN([artist], [name], [type], [height], [width]) ) AS PVT當您希望生成交叉分析報表以總結數據時，還可以使用 PIVOT。例如，使用 AdventureWorks 數據庫中的 Purchasing.PurchaSEOrderHeader 表（假設您希望返回每個雇員使用每個購買方法獲得的定單數量，並且用購買方法 ID 作為列的樞軸）。請記住，您只應當向 PIVOT 運算符提供相關數據。您可以使用派生表並編寫以下查詢：
SELECT EmployeeID, [1] AS SM1, [2] AS SM2, [3] AS SM3, [4] AS SM4, [5] AS SM5 FROM (SELECT PurchaseOrderID, EmployeeID, ShipMethodID FROM Purchasing.PurchaseOrderHeader) ORD PIVOT ( COUNT(PurchaSEOrderID) FOR ShipMethodID IN([1], [2], [3], [4], [5]) ) AS PVT以下為結果集：
EmployeeID SM1 SM2 SM3 SM4 SM5 ----------- ----------- ----------- ----------- ----------- ----------- 164 56 62 12 89 141 198 24 27 6 45 58 223 56 67 17 98 162 231 50 67 12 81 150 233 55 62 12 106 125 238 53 58 13
您正在看的SQLserver教程是:SQL Server 2005 Beta 2 Transact-SQL 增強功能 2。 102 134 241 50 59 13 108 130 244 55 47 17 93 148 261 58 54 11 120 117 264 50 58 15 86 151 266 58 68 14 116 144 274 24 26 6 41 63COUNT(PurchaSEOrderID) 函數為列表中的每個托運方法統計行數。請注意，PIVOT 不允許使用 COUNT(*)。列別名用來向結果列提供更具描述性的名稱。當您具有較少的托運方法並且它們的 ID 事先已知時，使用 PIVOT 在不同的列中顯示每個托運方法的定單計數是合理的。
還可以用從表達式中得到的值為樞軸。例如，假設您希望返回每個定單年中每個雇員的運費總值，並且用年份作為列的樞軸。定單年是從 OrderDate 列中得到的：
SELECT EmployeeID, [2001] AS Y2001, [2002] AS Y2002, [2003] AS Y2003, [2004] AS Y2004 FROM (SELECT EmployeeID, YEAR(OrderDate) AS OrderYear, Freight FROM Purchasing.PurchaSEOrderHeader) AS ORD PIVOT ( SUM(Freight) FOR OrderYear IN([2001], [2002], [2003], [2004]) ) AS PVT以下為結果集：
EmployeeID Y2001 Y2002 Y2003 Y2004 ----------- ----------- ----------- ----------- ------------ 164 509.9325 14032.0215 34605.3459 105087.7428 198 NULL 5344.4771 14963.0595 45020.9178 223 365.7019 12496.0776 37489.2896 117599.4156 231 6.8025 9603.0502 37604.3258 75435.8619 233 1467.1388 9590.7355 32988.0643 98603.745 238 17.3345 9745.1001 37836.583 100106.3678 241 221.1825 6865.7299 35559.3883 114430.983 244 5.026 5689.4571 35449.316 74690.3755 261 NULL 10483.27 32854.9343 73992.8431 264 NULL 10337.3207 37170.1957 82406.4474 266 4.2769 9588.8228 38533.9582 115291.2472 274 NULL 1877.2665 13708.9336 41011.3821交叉分析報表在數據倉庫方案中很常見。請考慮下面的 OrdersFact 表（您用 AdventureWorks 中的銷售定單和銷售定單詳細信息數據填充該表）：
CREATE TABLE OrdersFact ( OrderID INT NOT NULL, ProductID INT NOT NULL, CustomerID NCHAR(5) NOT NULL, OrderYear INT NOT NULL, OrderMonth INT NOT NULL, OrderDay INT NOT NULL, Quantity INT NOT NULL, PRIMARY KEY(OrderID, ProductID) ) INSERT INTO OrdersFact SELECT O.SalesOrderID, OD.ProductID, O.CustomerID, YEAR(O.OrderDate) AS OrderYear, MONTH(O.OrderDate) AS OrderMonth, DAY(O.OrderDate) AS OrderDay, OD.OrderQty FROM Sales.SalesOrderHeader AS O JOIN Sales.SalesOrderDetail AS OD ON O.SalesOrderID = OD.SalesOrderID要獲得每個年份和月份的總數量，並且在行中返回年份，在列中返回月份，則請使用以下查詢：
SELECT * FROM (SELECT OrderYear, OrderMonth,Quantity FROM OrdersFact) AS ORD PIVOT ( SUM(Quantity) FOR OrderMonth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS PVT
以下為結果集：
OrderYear 1 2 3 4 5 6 7 8 9 10 11 12 ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- 2001 NULL NULL NULL NULL NULL NULL 966 2209 1658 1403 3132 2480 2002 1040 2303 1841 1467 3179 2418 7755 11325 9066 5584 8268 6672 2003 3532 5431 4132 5694 8278 6444 11288 18986 18681 11607 14771 15855 2004 9227 10999 11314 12239 15656 15805 2209 NULL NULL NULL NULL NULL對於年份和月份之間不存在的交點，PIVOT 返回空值。如果某個年份出現在輸入表表達式（派生表 ORD）中，則它會出現在結果中，而不管它是否與任何指定的月份存在交點。這意味著，如果您未指定所有現有月份，則可能獲得在所有列中都含有 NULL 的行。但是，結果中的空值未必代表不存在的交點。它們可能產生自數量列中的基礎空值，除非該列不允許使用空值。如果您希望重寫 NULL 並且改而考慮另一個值（例如 0），則可以通過在選擇列表中使用 ISNULL() 函數做到這一點：
SELECT OrderYear, ISNULL([1], 0) AS M01, ISNULL([2], 0) AS M02, ISNULL([3], 0) AS M03, ISNULL([4], 0) AS M04, ISNULL([5], 0) AS M05, ISNULL(
您正在看的SQLserver教程是:SQL Server 2005 Beta 2 Transact-SQL 增強功能 2。[6], 0) AS M06, ISNULL([7], 0) AS M07, ISNULL([8], 0) AS M08, ISNULL([9], 0) AS M09, ISNULL([10], 0) AS M10, ISNULL([11], 0) AS M11, ISNULL([12], 0) AS M12 FROM (SELECT OrderYear, OrderMonth, Quantity FROM OrdersFact) AS ORD PIVOT ( SUM(Quantity) FOR OrderMonth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS PVT以下為結果集：
OrderYear 1 2 3 4 5 6 7 8 9 10 11 12 ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- 2001 0 0 0 0 0 0 966 2209 1658 1403 3132 2480 2002 1040 2303 1841 1467 3179 2418 7755 11325 9066 5584 8268 6672 2003 3532 5431 4132 5694 8278 6444 11288 18986 18681 11607 14771 15855 2004 9227 10999 11314 12239 15656 15805 2209 0 0 0 0 0歡迎光臨學網,點擊這裡查看更多文章教程 [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13] [14] [15] [16] [17] [18] [19] [20] [21]
在派生表中使用 ISNULL(Quantity, 0) 時，只會處理 Quantity 列中的基礎空值（如果該列存在），而不會處理 PIVOT 為不存在的交點生成的空值。
假設您希望針對 2003 年和 2004 年的第一個季度中的年份值和月份值組合返回范圍 1 到 9 中的每個客戶 ID 的總數量。要在行中獲得年份值和月份值，在列中獲得客戶 ID，請使用以下查詢：
SELECT * FROM (SELECT CustomerID, OrderYear, OrderMonth, Quantity FROM OrdersFact WHERE CustomerID BETWEEN 1 AND 9 AND OrderYear IN(2003, 2004) AND OrderMonth IN(1, 2, 3)) AS ORD PIVOT ( SUM(Quantity) FOR CustomerID IN([1],[2],[3],[4],[5],[6],[7],[8],[9]) ) AS PVT以下為結果集：
OrderYear OrderMonth 1 2 3 4 5 6 7 8 9 ----------- ----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- 2003 1 NULL NULL NULL 105 NULL NULL 8 NULL NULL 2004 1 NULL NULL NULL 80 NULL NULL NULL NULL NULL 2003 2 NULL 5 NULL NULL NULL NULL NULL NULL 15 2004 2 NULL 10 NULL NULL NULL NULL NULL 6 3 2003 3 NULL NULL 105 NULL 15 NULL NULL NULL NULL 2004 3 NULL NULL 103 NULL 25 4 NULL NULL NULL在該示例中，隱含的 group-by 列表為 OrderYear 和 OrderMonth，因為 CustomerID 和 Quantity 分別被用作樞軸列和值列。
但是，如果您希望年份值和月份值的組合顯示為列，則必須首先自己串聯它們，然後再將它們傳遞給 PIVOT 運算符，因為只能有一個樞軸列：
SELECT * FROM (SELECT CustomerID, OrderYear*100+OrderMonth AS YM, Quantity FROM OrdersFact WHERE CustomerID BETWEEN 1 AND 9 AND OrderYear IN(2003, 2004) AND OrderMonth IN(1, 2, 3)) AS ORD PIVOT ( SUM(Quantity) FOR YM IN([200301],[200302],[200303],[200401],[200402],[200403]) ) AS PVT以下為結果集：
CustomerID 200301 200302 200303 200401 200402 200403 ---------- ------ ------ ------ ------ ------ ------ 2 NULL 5 NULL NULL 10 NULL 3 NULL NULL 105 NULL NULL 103 6 NULL NULL NULL NULL NULL 4 4 105 NULL NULL 80 NULL NULL 8 NULL NULL NULL NULL 6 NULL 5 NULL NULL 15 NULL NULL 25 7
8 NULL NULL NULL NULL NULL 9 NULL 15 NULL NULL 3 NULL
UNPIVOT
UNPIVOT 運算符使您可以標准化預先旋轉的數據。UNPIVOT 運算符的語法和元素與 PIVOT 運算符類似。
例如，請考慮上一節中的 AuctionItems 表：itemid itemtype whenmade initialprice ----------- ------------------------ ----------- -------------- 1 Wine 1822 3000.0000 2 Wine 1807 500.0000 3 Chair 1753 800000.0000 4 Ring -501 1000000.0000 5 Painting 1873 8000000.0000 6 Painting 1889 8000000.0000
假設您希望每個屬性出現在不同的行中（類似於在 Ite
您正在看的SQLserver教程是:SQL Server 2005 Beta 2 Transact-SQL 增強功能 2。mAttributes 表中保存屬性的方式）：itemid attribute value ----------- --------------- ------- 1 itemtype Wine 1 whenmade 1822 1 initialprice 3000.00 2 itemtype Wine 2 whenmade 1807 2 initialprice 500.00 3 itemtype Chair 3 whenmade 1753 3 initialprice 800000.00 4 itemtype Ring 4 whenmade -501 4 initialprice 1000000.00 5 itemtype Painting 5 whenmade 1873 5 initialprice 8000000.00 6 itemtype Painting 6 whenmade 1889 6 initialprice 8000000.00在 UNPIVOT 查詢中，您希望將列 itemtype、whenmade 和 initialprice 旋轉到行。每個行都應當具有項 ID、屬性和值。您必須提供的新的列名稱為 attribute 和 value。它們對應於 PIVOT 運算符中的 pivot_column 和 value_column。attribute 列應當獲得您希望旋轉的實際列名稱（itemtype、whenmade 和 initialprice）作為值。value 列應當將來自三個不同源列中的值放到一個目標列中。為了有助於進行說明，首先介紹一個無效的 UNPIVOT 查詢版本，然後介紹一個應用了某些限制的有效版本：
SELECT itemid, attribute,
value FROM AuctionItems UNPIVOT ( value FOR attribute IN([itemtype], [whenmade], [initialprice]) ) AS UPV
作為 PIVOT 運算符的參數，您為後面跟 FOR 子句的 value_column（在該示例中為 value）提供一個名稱。在 FOR 子句後面，為 pivot_column（在該示例中為 attribute）提供一個名稱，然後提供一個 IN 子句，其中帶有您希望獲得以作為 pivot_column 中的值的源列名稱的列表。在 PIVOT 運算符中，該列列表被引用為
。該查詢生成以下錯誤： .Net SqlClient Data Provider:Msg 8167, Level 16, State 1, Line 1 Type of column 'whenmade' conflicts with the type of other columns specifIEd in the UNPIVOT list.目標 value 列包含源自多個不同源列（那些出現在
中的列）的值。因為所有列值的目標是單個列，所以 UNPIVOT 要求 中的所有列都具有相同的數據類型、長度和精度。要滿足該限制，可以向 UNPIVOT 運算符提供一個表表達式，以便將這三個列轉換為相同的數據類型。sql_variant 數據類型是一個良好的候選類型，因為您可以將不同的源列轉換為相同的數據類型，並且仍然保留它們的原始數據類型。應用該限制，您可以按如下方式修改上一個查詢並獲得所需的結果： SELECT itemid, attribute, value FROM (SELECT itemid, CAST(itemtype AS SQL_VARIANT) AS itemtype, CAST(whenmade AS SQL_VARIANT) AS whenmade, CAST(initialprice AS SQL_VARIANT) AS initialprice FROM AuctionItems) AS ITM UNPIVOT ( value FOR attribute IN([itemtype], [whenmade], [initialprice]) ) AS UPV結果 attribute 列的數據類型為 sysname。這是 SQL Server 用於存儲對象名稱的數據類型。
請注意，UNPIVOT 運算符從結果中消除了 value 列中的空值；因此，不能將其視為 PIVOT 運算符的嚴格逆操作。
在將 AuctionItems 中的列旋轉為行之後，您現在可以將 UNPIVOT 操作的結果與 ItemAttributes 中的行合並，以提供統一的結果：
SELECT itemid, attribute, value FROM (SELECT itemid, CAST(itemtype AS SQL_VARIANT) AS itemtype, CAST(whenmade AS SQL_VARIANT) AS whenmade, CAST(initialprice AS SQL_VARIANT) AS initialprice FROM AuctionItems) AS ITM UNPIVOT ( value FOR attribute IN([itemtype], [whenmade], [initialprice]) ) AS UPV UNION ALL SELECT * FROM ItemAttributes ORDER BY itemid, attribute以下為結果集：
itemid attribute value ----------- --------------- ------------- 1 color Red 1 initialprice 3000.00 1 itemtype W
ine 1 manufacturer ABC 1 type Pinot Noir 1 whenmade 1822 2 color Red 2 initialprice 500.00 2 itemtype Wine 2 manufacturer XYZ 2 type Porto 2 whenmade 1807 3 initialprice 800000.00 3 itemtype Chair 3 material Wood 3 padding Silk 3 wh
您正在看的SQLserver教程是:SQL Server 2005 Beta 2 Transact-SQL 增強功能 2。enmade 1753 4 initialprice 1000000.00 4 inscription One ring 4 itemtype Ring 4 material Gold 4 size 10 4 whenmade -501 5 height 19.625 5 initialprice 8000000.00 5 itemtype Painting 5 name Field of PoppIEs 5 artist Claude Monet 5 type Oil 5 whenmade 1873 5 width 25.625 6 height 28.750 6 initialprice 8000000.00 6 itemtype Painting 6 name The StarryNight 6 artist Vincent Van Gogh 6 type Oil 6 whenmade 1889 6 width 36.250APPLY
APPLY 關系運算符使您可以針對外部表表達式的每個行調用指定的表值函數一次。您可以在查詢的 FROM 子句中指定 APPLY，其方式與使用 JOIN 關系運算符類似。APPLY 具有兩種形式：CROSS APPLY 和 OUTER APPLY。通過 APPLY 運算符，SQL Server 2005 Beta 2 使您可以在相關子查詢中引用表值函數。
CROSS APPLY
CROSS APPLY 為外部表表達式中的每個行調用表值函數。您可以引用外部表中的列作為該表值函數的參數。CROSS APPLY 從該表值函數的單個調用所返回的所有結果中返回統一的結果集。如果該表值函數對於給定的外部行返回空集，則不會在結果中返回該外部行。例如，以下表值函數接受兩個整數作為參數，並且返回帶有一個行的表 — 該表用最小值和最大值作為列：
CREATE FUNCTION dbo.fn_scalar_min_max(@p1 AS INT, @p2 AS INT) RETURNS TABLE AS RETURN SELECT CASE WHEN @p1 < @p2 THEN @p1 WHEN @p2 < @p1 THEN @p2 ELSE COALESCE(@p1, @p2) END AS mn, CASE WHEN @p1 > @p2 THEN @p1 WHEN @p2 > @p1 THEN @p2 ELSE COALESCE(@p1,@p2) END AS mx GO SELECT * FROM fn_scalar_min_max(10, 20)
以下為結果集：
mn mx ----------- ----------- 10 20給定下面的 T1 表：
CREATE TABLE T1 ( col1 INT NULL, col2 INT NULL ) INSERT INTO T1 VALUES(10, 20) INSERT INTO T1 VALUES(20, 10) INSERT INTO T1 VALUES(NULL, 30) INSERT INTO T1 VALUES(40, NULL) INSERT INTO T1 VALUES(50, 50)您希望為 T1 中的每個行調用 fn_scalar_min_max。您可以按如下方式編寫 CROSS APPLY 查詢：
SELECT * FROM T1 CROSS APPLY fn_scalar_min_max(col1, col2) AS M以下為結果集：
col1 col2 mn mx ----------- ----------- ----------- ----------- 10 20 10 20 20 10 10 20 NULL 30 30 30 40 NULL 40 40 50 50 50 50如果該表值函數為特定的外部行返回多個行，則該外部行被多次返回。考慮在本文前面的遞歸查詢和常見表表達式一節中使用的 Employees 表（“雇員組織結構圖”方案）。在同一數據庫中，您還創建了以下 Departments 表：
CREATE TABLE Departments ( deptid INT NOT NULL PRIMARY KEY, deptname VARCHAR(25) NOT NULL, deptmgrid INT NULL REFERENCES Employees ) SET NOCOUNT ON INSERT INTO Departments VALUES(1, 'HR', 2) INSERT INTO Departments VALUES(2, 'Marketing', 7) INSERT INTO Departments VALUES(3, 'Finance', 8) INSERT INTO Departments VALUES(4, 'R&D', 9) INSERT INTO Departments VALUES(5, 'Training', 4) INSERT INTO Departments VALUES(6, 'Gardening', NULL)大多數部門都具有一個與 Employees 表中的某個雇員相對應的經理 ID，但是像 Gardening 部門一樣，有些部門可能沒有經理。請注意，Employees 表中的經理必然管理某個部門。以下表值函數接受雇員 ID 作為參數，並且返回該雇員及其所有級別的所有下屬：
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE ( empid INT NOT NULL, empname VARCHAR(25) NOT NULL, mgrid INT NULL, lvl INT NOT NULL ) AS BEGIN WITH Employees_Subtree(empid, empname, mgrid,
lvl) AS ( -- Anchor Member (AM) SELECT empid, empname, mgrid, 0 FROM employees WHERE empid = @empid UNION all -- Recursive Member (RM) SELECT e.e
您正在看的SQLserver教程是:SQL Server 2005 Beta 2 Transact-SQL 增強功能 2。mpid, e.empname, e.mgrid, es.lvl+1 FROM employees AS e JOIN employees_subtree AS es ON e.mgrid = es.empid ) INSERT INTO @TREE SELECT * FROM Employees_Subtree RETURN END GO要為每個部門的經理返回所有級別的所有下屬，請使用以下查詢：
SELECT * FROM Departments AS D CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST以下為結果集：
deptid deptname deptmgrid empid empname mgrid lvl ----------- ---------- ----------- ----------- ---------- ----------- --- 1 HR 2 2 Andrew 1 0 1 HR 2 5 Steven 2 1 1 HR 2 6 Michael 2 1 2 Marketing 7 7 Robert 3 0 2 Marketing 7 11 David 7 1 2 Marketing 7 12 Ron 7 1 2 Marketing 7 13 Dan 7 1 2 Marketing 7 14 James 11 2 3 Finance 8 8 Laura 3 0 4 R&D 9 9 Ann 3 0 5 Training 4 4 Margaret 1 0 5 Training 4 10 Ina 4 1這裡需要注意兩個事情。第一，Departments 中的每個行都被復制與從 fn_getsubtree 中為該部門的經理返回的行數一樣多的次數。第二，Gardening 部門不會出現在結果中，因為 fn_getsubtree 為其返回空集。
CROSS APPLY 運算符的另一個實際運用可以滿足以下常見請求：為每個組返回 n 行。例如，以下函數返回給定客戶的請求數量的最新定單：
USE AdventureWorks GO CREATE FUNCTION fn_getnorders(@custid AS INT, @n AS INT) RETURNS TABLE AS RETURN SELECT TOP(@n) * FROM Sales.SalesOrderHeader WHERE CustomerID = @custid ORDER BY OrderDate DESC GO
使用 CROSS APPLY 運算符，可以通過下面的簡單查詢獲得每個客戶的兩個最新定單：
SELECT O.* FROM Sales.Customer AS C CROSS APPLY fn_getnorders(C.CustomerID, 2) AS O有關 TOP 增強功能的詳細信息，請參閱下文中的“TOP 增強功能”。
OUTER APPLY
OUTER APPLY 非常類似於 CROSS APPLY，但是它還從表值函數為其返回空集的外部表中返回行。空值作為與表值函數的列相對應的列值返回。例如，修改針對上一節中的 Departments 表的查詢以使用 OUTER APPLY 而不是 CROSS APPLY，並且注意輸出中的最後一行：
SELECT * FROM Departments AS D OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST以下為結果集：
deptid deptname deptmgrid empid empname mgrid lvl ----------- ---------- ----------- ----------- ---------- ----------- --- 1 HR 2 2 Andrew 1 0 1 HR 2 5 Steven 2 1 1 HR 2 6 Michael 2 1 2 Marketing 7 7 Robert 3 0 2 Marketing 7 11 David 7 1 2 Marketing 7 12 Ron 7 1 2 Marketing 7 13 Dan 7 1 2 Marketing 7 14 James 11 2 3 Finance 8 8 Laura 3 0 4 R&D 9 9 Ann 3 0 5 Training 4 4 Margaret 1 0 5 Training 4 10 Ina 4 1 6 Gardening NULL NULL NULL NULL NULL相關子查詢中的表值函數
在 SQL Server 2000 中，不能在相關子查詢內部引用表值函數。與提供 APPLY 關系運算符一道，該限制在 SQL Server 2005 Beta 2 中被移除。現在，在子查詢內部，可以向表值函數提供外部查詢中的列作為參數。例如，如果您希望只返回那些經理至少具有三名雇員的部門，則可以編寫以下查詢：
SELECT * FROM Departments AS D WHERE (SELECT COU
您正在看的SQLserver教程是:SQL Server 2005 Beta 2 Transact-SQL 增強功能 2。NT(*) FROM fn_getsubtree(D.deptmgrid)) >= 3 deptid deptname deptmgrid ----
------- ------------------------- ----------- 1 HR 2 2 Marketing 7
對新的 DRI 操作的支持：
SET DEFAULT 和 SET NULLANSI SQL 定義了四個可能的引用操作，以支持 FOREIGN KEY 約束。您可以指定這些操作，以表明您希望系統如何響應針對由外鍵引用的表的 DELETE 或 UPDATE 操作。SQL Server 2000 支持這些操作中的兩個：NO ACTION 和 CASCADE。SQL Server 2005 Beta 2 添加了對 SET DEFAULT 和 SET NULL 引用操作的支持。
SET DEFAULT 和 SET NULL 引用操作擴展了聲明性引用完整性 (DRI) 功能。您可以在外鍵聲明中將這些選項與 ON UPDATE 和 ON DELETE 子句結合使用。SET DEFAULT 意味著，當您在被引用的表中刪除行 (ON DELETE) 或更新被引用的鍵 (ON UPDATE) 時，SQL Server 會將引用表中的相關行的引用列值設置為該列的默認值。類似地，如果您使用 SET NULL 選項，則 SQL Server 可以通過將值設置為 NULL 進行反應（前提是引用列允許使用空值）。
例如，以下 Customers 表具有三個真實客戶和一個虛擬客戶：
CREATE TABLE Customers ( customerid CHAR(5) NOT NULL, /* other columns */ CONSTRAINT PK_Customers PRIMARY KEY(customerid) ) INSERT INTO Customers VALUES('DUMMY') INSERT INTO Customers VALUES('FRIDA') INSERT INTO Customers VALUES('GNDLF') INSERT INTO Customers VALUES('BILLY')Orders 表跟蹤定單。不一定非要將定單分配給真實客戶。如果您輸入一個定單並且未指定客戶 ID，則默認情況下會將 DUMMY 客戶 ID 分配給該定單。在從 Customers 表中進行刪除時，您希望 SQL Server 在 Orders 中的相關行的 customerid 列中設置 NULL。customerid 列中含有 NULL 的定單成為“孤兒”，也就是說，它們不屬於任何客戶。假設您還希望允許對 Customers 中的 customerid 列進行更新。您可能希望將對 Orders 中的相關行進行的更新級聯，但是假設公司的業務規則另行規定：應當將屬於 ID 被更改的客戶的定單與默認客戶 (DUMMY) 相關聯。在對 Customers 中的 customerid 列進行更新時，您希望 SQL Server 將默認值 'DUMMY' 設置為 Orders 中的相關客戶 ID (customerid)。您用外鍵按如下方式創建 Orders 表，並且用一些定單填充它：
CREATE TABLE Orders ( orderid INT NOT NULL, customerid CHAR(5) NULL DEFAULT('DUMMY'), orderdate DATETIME NOT NULL, CONSTRAINT PK_Orders PRIMARY KEY(orderid), CONSTRAINT FK_Orders_Customers FOREIGN KEY(customerid) REFERENCES Customers(customerid) ON DELETE SET NULL ON UPDATE SET DEFAULT ) INSERT INTO Orders VALUES(10001, 'FRIDA', '20040101') INSERT INTO Orders VALUES(10002, 'FRIDA', '20040102') INSERT INTO Orders VALUES(10003, 'BILLY', '20040101') INSERT INTO Orders VALUES(10004, 'BILLY','20040103') INSERT INTO Orders VALUES(10005, 'GNDLF', '20040104') INSERT INTO Orders VALUES(10006, 'GNDLF', '20040105')
要測試 SET NULL 和 SET DEFAULT 選項，請發出下列 DELETE 和 UPDATE 語句：
DELETE FROM Customers WHERE customerid = 'FRIDA' UPDATE Customers SET customerid = 'DOLLY' WHERE customerid = 'BILLY'結果，FRIDA 的定單被分配 customerid 列中的空值，而 BILLY 的定單被分配 DUMMY：
orderid customerid orderdate ----------- ---------- ---------------------- 10001 NULL 1/1/2004 12:00:00 AM 10002 NULL 1/2/2004 12:00:00 AM 10003 DUMMY 1/1/2004 12:00:00 AM 10004 DUMMY 1/3/2004 12:00:00 AM 10005 GNDLF 1/4/2004 12:00:00 AM 10006 GNDLF 1/5/2004 12:00:00 AM請注意，如果您使用 SET DEFAULT 選項，引用列具有非空默認值且該值在被引用的表中不具有相應值，則當您發出觸發操作時，將獲得錯誤。例如，如果您從 Customers 中刪除 DUMMY 客戶，然後將 GNDLF 的 customerid 更新為 GLDRL，則會獲得錯誤。UPDATE 觸發一個 SET DEFAULT 操作，該操作試圖向 GNDLF 的原始定單分配在 Customers 中不具有相應行的 DUMMY 客戶 ID：
DELETE FROM Customers WHERE customerid = 'DUMMY' UPDATE Customers SET customerid = 'GLDRL' WHERE customerid = 'GNDLF' .Net SqlClIEnt Data Provider: Msg 547, Level 16, State 0, Line 1 UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Orders_Customers'. The conflict occurred in database 'tempdb', table 'Customers', column 'customerid'. The statement has been terminated.通過查看 sys.foreign_keys，您可以找到有關外鍵的詳細信息，包括它們的已定義的引用操作。
性能和錯誤處理增強功能
本節討論用來解決以前版本的 SQL Serv
您正在看的SQLserver教程是:SQL Server 2005 Beta 2 Transact-SQL 增強功能 2。er 中的性能問題的增強功能，提高您的數據加載能力，並且顯著改善您的錯誤管理能力。這些增強功能包括 BULK 行集提供程序和 TRY...CATCH 錯誤處理結構。BULK 行集提供程序
BULK 是 OPENROWSET 函數中指定的新的行集提供程序，它使您可以訪問關系格式的文件數據。為了從文件中檢索數據，您可以指定 BULK 選項、文件名以及用 bcp.exe 創建或手動創建的格式文件。您可以在從 OPENROWSET 中返回的表的別名後面的括號中，指定結果列的名稱。
以下為您可以用 OPENROWSET 指定的所有選項的新語法：
OPENROWSET ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'passWord' | 'provider_string' } , { [ catalog. ] [ schema. ] object | 'query' }
| BULK 'data_filename', {FORMATFILE = 'format_file_path' [, ] | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB} } ) ::= [ , CODEPAGE = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] [ , FIRSTROW = first_row ] [ , LASTROW = last_row ] [ , ROWS_PER_BATCH = 'rows_per_batch'] [ , MAXERRORS = 'max_errors'] [ , ERRORFILE ='file_name'] } )
例如，以下查詢從文本文件“c:\temp\textfile1.txt”中返回三個列，並且向結果列提供了列別名 col1、col2 和 col3：
SELECT col1, col2, col3 FROM OPENROWSET(BULK 'c:\temp\textfile1.txt', FORMATFILE = 'c:\temp\textfile1.fmt') AS C(col1, col2, col3)請注意，當您使用 BULK 選項時，也必須指定格式文件，除非您使用我稍後將描述的 SINGLE_BLOB、SINGLE_CLOB 或 SINGLE_NCLOB 選項。因此，無須指定數據文件類型、字段終止符或行終止符。您可以根據需要與 FORMATFILE 一起指定的其他選項包括：CODEPAGE、FIRSTROW、LASTROW、ROW_PER_BATCH、MAXERRORS 和 ERRORFILE。大多數選項可以通過 SQL Server 2000 中的 BULK INSERT 命令使用。ERRORFILE 選項在概念上是新的。該文件包含零個或更多個具有來自輸入數據文件的格式化錯誤的行（即，這些行無法轉換為 OLEDB 行集）。這些行從數據文件中“按原樣”復制到該錯誤文件中。在修復該錯誤之後，數據就會立即具有預期的格式，因此可以使用相同的命令容易地重新加載它。錯誤文件是在命令執行開始時創建的。如果該文件已經存在，則會引發錯誤。通過觀察該文件中的行，可以容易地識別失敗的行，但是沒有辦法知道失敗的原因。為了解決該問題，自動創建一個擴展名為 .ERROR.txt 的控制文件。該文件引用 ERRORFILE 中的每個行並且提供錯誤診斷。
您可以使用 BULK 行集提供程序，用從 OPENROWSET 返回的結果填充一個表，並且為批量加載操作指定表選項。例如，以下代碼將上一個查詢的結果加載到表 MyTable 中，並請求禁用目標表中的約束檢查：
INSERT INTO MyTable WITH (IGNORE_CONSTRAINTS) SELECT col1, col2, col3 FROM OPENROWSET(BULK 'c:\temp\textfile1.txt', FORMATFILE = 'c:\temp\textfile1.fmt') AS C(col1, col2, col3)除了 IGNORE_CONSTRAINTS 選項以外，可以在加載操作中指定的其他表提示包括：BULK_KEEPIDENTITY、BULK_KEEPNULLS 和 IGNORE_TRIGGERS。
您還可以使用 BULK 提供程序，通過指定下列選項之一，將文件數據作為某個大型對象類型的單個列值返回：用於字符數據的 SINGLE_CLOB、用於 Unicode 數據的 SINGLE_NCLOB 以及用於二進制數據的 SINGLE_BLOB。當您使用上述選項之一時，您沒有指定格式文件。您可以將文件加載（使用 INSERT 或 UPDATE 語句）到下列數據類型之一的大型對象列中：VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX) 或 XML。在下文中，您可以找到有關變長列的 MAX 說明符以及有關 XML 數據類型的詳細信息。
作為將文件加載到大型列中的示例，以下 UPDATE 語句將文本文件“c:\temp\textfile101.txt”加載到客戶 101 的表 CustomerData 中的列 txt_data 中：
UPDATE CustomerData SET txt_data = (SELECT txt_data FROM OPENROWSET( BULK 'c:\temp\textfile101.txt',SINGLE_CLOB) AS F(txt_data)) WHERE custid = 101
請注意，一次只能更新一個大型列。
以下示例說明了如何使用 INSERT 語句將客戶 102 的二進制文件加載到大型列中：
INSERT INTO CustomerData(custid, binary_data) SELECT 102 AS custid, binary_data FROM OPENROWSET( BULK 'c:\temp\binfile102.dat', SINGLE_BLOB) AS F(binary_data)異常處理
SQL Server 2005 Beta 2 以 TRY...CATCH Transact-SQL 結構的形式引入了一種簡單但非常強大的異常處理機制。
以前版本的 SQL Server 要求在每個懷疑可能出錯的語句之後包含錯誤處理代碼。要將錯誤檢查代碼集中在一起，必須使用標簽和 GOTO 語句。此外，諸如數據類型轉換錯誤之類的錯誤會導致批處理終止；因此，無法用 Transact-SQL 捕獲這些錯誤。SQL Server 2005 Beta 2 解決了這些問題中的很多問題。
現在可以捕獲和處理過去會導致批處理終止的錯誤，前提是這些錯誤不會導致連接中斷（通常是嚴重度為 21 及以上的錯誤，例如，表或數據庫完整性可疑、硬件錯誤等等）。
在 BEGIN TRY/END TRY 塊中編寫您希望執行的代碼，並且後面緊跟位於 BEGIN CATCH/END CATCH 塊中的錯誤處理代碼。請注意，TRY 塊必須具有相應的 CATCH 塊；否則，您將得到語法錯誤。作為一個簡單的示例，請考慮以下 Employees 表：
CREATE TABLE Employees ( empid INT NOT NULL, empname VARCHAR(25) NOT NULL, mgrid INT NULL, /* other columns */ CONSTRAINT PK_Employees PRIMARY KEY(empid), CONSTRAINT CHK_Employees_empid CHECK(empid > 0), CONSTRAINT FK_Employees_Employees FOREIGN KEY(mgrid) REFERENCES Employees(empid) )您希望編寫代碼以便將新的雇員行插入到該表中。您還希望用一些糾正性的活動響應失敗
您正在看的SQLserver教程是:SQL Server 2005 Beta 2 Transact-SQL 增強功能 2。情況。按如下方式使用新的 TRY...CATCH 結構：BEGIN TRY INSERT INTO Employees(empid, empname, mgrid) VALUES(1, 'Emp1'NULL) PRINT 'After INSERT.' END TRY BEGIN CATCH PRINT 'INSERT failed.' /* perform corrective activity */ END CATCH當您首次運行該代碼時，應當獲得輸出“After INSERT”。當您第二次運行它時，應當獲得輸出“INSERT Failed”。
如果 TRY 塊中的代碼沒有任何錯誤地完成，則控制被傳遞給相應的 CATCH 塊後面的第一個語句。當 TRY 塊中的語句失敗時，控制被傳遞給相應的 CATCH 塊中的第一個語句。請注意，如果錯誤被 CATCH 塊捕獲，則它不會返回到調用應用程序。如果您還希望應用程序獲得錯誤信息，則必須自己將該信息提供給應用程序（例如，使用 RAISERROR 或作為查詢的結果集）。所有錯誤信息都借助於四個新的函數在 CATCH 塊中提供給您：ERROR_NUMBER()、ERROR_MESSAGE()、ERROR_SEVERITY() 和 ERROR_STATE()。這些函數可以在 CATCH 塊中您喜歡的任何位置多次查詢，並且它們的值保持不變。這與除 DECLARE 以外還受到任何語句影響的 @@error 函數（因此必須在 CATCH 塊的第一個語句中查詢它）相反。ERROR_NUMBER() 可以用作 @@error 的替代函數，而其他三個函數則完全按照由錯誤生成的樣子向您提供該信息的其余部分。歡迎光臨學網,點擊這裡查看更多文章教程 [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13] [14] [15] [16] [17] [18] [19] [20] [21]
此類信息在低於 SQL Server 2005 的 SQL Server 版本中無法獲得。
如果在批處理或例程（存儲過程、觸發器、用戶定義的函數、動態代碼）中生成了未處理的錯誤，並且某個較高級別的代碼在 TRY 塊內部調用了該批處理或例程，則控制被傳遞給該較高級別的相應 CATCH 塊。如果該較高級別沒有在 TRY 塊內調用該內部級別，則 SQL Server 將繼續在調用堆棧中的較高級別中查找 TRY 塊，並且會將控制傳遞給找到的第一個 TRY...CATCH 結構的 CATCH 塊。如果未找到，則將錯誤返回給調用應用程序。
作為一個更詳細的示例，以下代碼根據導致失敗的錯誤的類型做出不同的反應，並且輸出消息以表明代碼的哪些部分已經被激活：
PRINT 'Before TRY...CATCH block.' BEGIN TRY PRINT ' Entering TRY block.' INSERT INTO Employees(empid, empname, mgrid) VALUES(2, 'Emp2', 1) PRINT ' After INSERT.' PRINT ' Exiting TRY block.' END TRY BEGIN CATCH PRINT ' Entering CATCH block.' IF ERROR_NUMBER() = 2627 BEGIN PRINT ' Handling PK violation...' END ELSE IF ERROR_NUMBER() = 547 BEGIN PRINT ' Handling CHECK/FK constraint violation...' END ELSE IF ERROR_NUMBER() = 515 BEGIN PRINT ' Handling NULL violation...' END ELSE IF ERROR_NUMBER() = 245 BEGIN PRINT ' Handling conversion error...' END ELSE BEGIN PRINT ' Handling unknown error...' END PRINT ' Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) PRINT ' Error Message: ' + ERROR_MESSAGE() PRINT ' Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) PRINT ' Error State : ' + CAST(ERROR_STATE() AS VARCHAR(10)) PRINT ' Exiting CATCH block.' END CATCH PRINT 'After TRY...CATCH block.'請注意，ERROR_NUMBER() 函數在 CATCH 塊中被多次調用，並且它總是返回導致控制傳遞給該 CATCH 塊的錯誤的編號。該代碼將雇員 2 作為先前插入的雇員 1 的下屬插入，並且在首次運行時應當沒有任何錯誤地完成，並生成以下輸出：
Before TRY...CATCH block. Entering TRY block. After INSERT. Exiting TRY block. After TRY...CATCH block.請注意，CATCH 塊被跳過。第二次運行該代碼時，應當生成以下輸出：
Before TRY...CATCH block. Entering TRY block. Entering CATCH block. Handling PK violation... Error Number: 2627 Error Message: Violation of PRIMARY KEY constraint 'PK_Employees'. Cannot insert duplicate key in object 'Employees'. Error Severity: 14 Error State : 1 Exiting CATCH block. After TRY...CATCH block.請注意，TRY 塊被進入，但未完成。作為主鍵沖突錯誤的結果，控制被傳遞給 CATCH 塊，該塊會識別並處理該錯誤。類似地，如果您分配的值不是有效的雇員 ID 數據，例如，0（它違反了 CHECK 約束）、NULL（它不允許在 employeeid 中使用）以及 'a,'（它無法轉換為 INT），則您會得到相應的錯誤，並且會激活相應的處理代碼。
如果您要在 TRY 塊中使用顯式事務，則您可能希望在 CATCH 塊中的錯誤處理代碼中調查事務狀態，以確定操作過程。SQL Server 2005 提供了新的函數 XACT_STATE() 以返回事務狀態。該函數可能返回的值為：0、-1 和 1。0 返回值意味著沒有打開任何事務。試圖提交或回滾該事務時，會生成錯誤。1 返回值意味著事務已打開，並且可以提交或回滾。您需要根據自己的需要和錯誤處理邏輯確定是提交還是回滾該事務。-1 返回值意味著事務已打開但處於無法提交的狀態 — 這是 SQL Server 2005 中引入的新的事務狀態。當生成可能會導致事務被中止的錯誤（通常，嚴重度為 17 或更高）時，TRY 塊內的事務會進入無法提交的狀態。無法提交的事務會保持所有打開的鎖，並且只允許您讀取數據。您不能提交任何需要寫事務日志的活動，這意味著當事務處於無法提交的狀態時，您無法更改數據。為了終止該事務，您必須發出回滾。您不能提交該事務，而只能在可以接受任何修改之前將其回滾。以下示例演示了如何使用 XACT_STATE() 函數：
BEGIN TRY BEGIN TRAN INSERT INTO Employees(empid, empname, mgrid) VALUES(3, 'Emp3', 1) /* other activity */ COMMIT TRAN PRINT 'Code completed successfully.' END TRY BEGIN CATCH PRINT 'Error: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))
您正在看的SQLserver教程是:SQL Server 2005 Beta 2 Transact-SQL 增強功能 2。+ ' found.' IF (XACT_STATE()) = -1 BEGIN PRINT 'Transaction is open but uncommittable.' /* ...investigate data... */ ROLLBACK TRANSACTION -- can only ROLLBACK /* ...handle the error... */ END ELSE IF (XACT_STATE()) = 1 BEGIN PRINT 'Transaction is open and committable.' /* ...handle error... */ COMMIT TRANSACTION -- or ROLLBACK END ELSE BEGIN PRINT 'No open transaction.' /* ...handle error... */ END END CATCHTRY 塊在顯式事務內部提交代碼。它插入一個新的雇員行，並且在同一事務內部執行其他一些活動。CATCH 塊輸出錯誤編號，並且調查事務狀態以確定操作過程。如果事務已打開並且無法提交，則 CATCH 塊會調查數據，回滾該事務，然後采取任何需要數據修改的糾正性措施。如果該事務已打開並且可以提交，則 CATCH 塊會處理錯誤並提交（也可能回滾）。如果沒有任何事務打開，則錯誤被處理。不會發出任何提交或回滾。如果您是首次運行該代碼，則會插入對應於雇員 3 的新的雇員行，並且代碼成功完成，產生以下輸出：
Code completed successfully.如果您是第二次運行該代碼，則會生成主鍵沖突錯誤，並且您會獲得以下輸出：
Error: 2627 found. Transaction is open and committable.其他影響 Transact-SQL 的 SQL Server 2005 Beta 2 功能
本節簡要描述 SQL Server 2005 Beta 2 中的其他影響 Transact-SQL 的增強功能。這包括對 TOP 進行的增強、帶結果的數據操縱語言 (DML)、動態列的 MAX 說明符、XML/XQuery、數據定義語言 (DDL) 觸發器、隊列和 SQL Server Service Broker 以及 DML 事件和通知。
TOP 增強功能
在 SQL Server 版本 7.0 和 SQL Server 2000 中，可以通過 TOP 選項限制 SELECT 查詢所返回的行數或百分比；但是，您必須提供一個常量作為參數。在 SQL Server 2005 Beta 2 中，TOP 用下列主要方式進行了增強：
•
現在可以指定一個數字表達式，以返回要通過查詢影響的行數或百分比，還可以根據情況使用變量和子查詢。
•
現在可以在 DELETE、UPDATE 和 INSERT 查詢中使用 TOP 選項。
使用 TOP 選項的查詢的新語法是：
SELECT [TOP () [PERCENT] [WITH TIES]] FROM ...[ORDER BY...] DELETE [TOP () [PERCENT]] FROM ... UPDATE [TOP () [PERCENT]] SET ... INSERT [TOP () [PERCENT]] INTO ...必須在括號中指定數字表達式。在 SELECT 查詢中支持不用括號指定常量的原因是為了保持向後兼容。表達式必須是獨立的 — 如果您使用子查詢，則它無法引用外部查詢中的表的列。如果您不指定 PERCENT 選項，則該表達式必須可以隱式轉換為 bigint 數據類型。如果您指定 PERCENT 選項，則該表達式必須可以隱式轉換為 float 並且落在范圍 0 到 100 之內。WITH TIES 選項和 ORDER BY 子句只在 SELECT 查詢中受到支持。
例如，以下代碼使用變量作為 TOP 選項的參數，並且返回指定數量的最新購買定單：
USE AdventureWorks DECLARE @n AS BIGINT SET @n = 2 SELECT TOP(@n) * FROM Purchasing.PurchaSEOrderHeader ORDER BY OrderDate DESC當您將所請求的行的數量作為存儲過程或用戶定義函數的參數時，該增強功能尤其有用。通過使用獨立的子查詢，您可以回答動態請求，例如，“計算每月定單的平均數量，並返回那麼多的最新定單”：
USE AdventureWorks SELECT TOP(SELECT COUNT(*)/DATEDIFF(month, MIN(OrderDate), MAX(OrderDate)) FROM Purchasing.PurchaseOrderHeader) * FROM Purchasing.PurchaSEOrderHeader ORDER BY OrderDate DESC較低版本的 SQL Server 中的 SET ROWCOUNT 選項使您可以限制受到查詢影響的行數。例如，SET ROWCOUNT 常用來定期清除多個小型事務而不是單個大型事務中的大量數據：
SET ROWCOUNT 1000 DELETE FROM BigTable WHERE datetimecol < '20000101' WHILE @@rowcount > 0 DELETE FROM BigTable WHERE datetimecol < '20000101' SET ROWCOUNT 0以該方式使用 SET ROWCOUNT，可以在清除過程中備份和回收事務日志，並且還可以防止鎖升級。現在可以這樣使用 TOP，而不是使用 SET ROWCOUNT：
DELETE TOP(1000) FROM BigTable WHERE datetimecol < '20000101' WHILE @@rowcount > 0 DELETE TOP(1000) FROM BigTable WHERE datetimecol < '20000101'當您使用 TOP 選項時，優化程序可以知道“行目標”是什麼以及到底是否使用了 TOP，從而使優化程序可以產生更有效的計劃。
盡管您可能認為不需要在 INSERT 語句中使用 TOP（因為您總是可以在 SELECT 查詢中指定它），但您可能會發現它在插入 EXEC 命令的結果或 UNION 操作的結果時很有用。例如：
INSERT TOP ... INTO ... EXEC ... INSERT TOP ... INTO ... SELECT ... FROM T1 UNION ALL SELECT ... FROM T2 ORDER BY ...帶結果的 DML
SQL Server 2005 引入了一個新的 OUTPUT 子句，以使您可以從修改語句（INSERT、UPDATE、DELETE）中將數據返回到表變量中。帶結果的 DML 的有用方案包括清除和存檔、消息處理應用程序以及其他方案。這一新的 OUTPUT 子句的語法為：
OUTPUTINTO @table_variable
