SQL Server 2012 T-SQL 新特性

標籤：數值   blog   te pro   number   stp   metadata   first   出現   ade   

原文:SQL Server 2012 T-SQL 新特性

序列 Sequence

SQL Server 現在將序列當成一個對象來實現，建立一個序列的例子文法如下：

CREATE SEQUENCE DemoSequenceSTART WITH 1INCREMENT BY 1;

使用序列的方法如下所表達的：

SELECT VALUE FOR DemoSequence

序列與以前的種子列(identity)的區別很明顯，種子列只限於當前列，而序列是一個對象層面的實現，則可以在多個表之間共用。這一點特點在管理軟體序號產生方面，是個不錯的開始。和種子列相似，序列也可以重設，例子如下

ALTER SEQUENCE Samples.IDLabelRESTART WITH 1 ;

序列的值可以使用整數型別，比如tinyint, smallint, int, bigint, decimal 或是小數精度為0的數實值型別。

序列的限制(limitation)有二個，一是序列不支援事務，即使事務中進行了復原(rollback)操作，序列仍然返回下一個元素。

第二，序列不支援SQL Server 複製(replication)，序列不會複製到訂閱的SQL Server執行個體中。如果一個表的預設值依賴一個序列，而序列又是不可複製的，這會導致訂閱的SQL Server出現指令碼錯誤。

 

資料分頁 Page Data

SQL Server一直在改善資料分頁方法，SQL Server 2005內建的row_number函數可以實現，例子代碼如下

SELECT *FROM (SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) AS sequencenumber, *FROM Customers) AS TempTableWHERE sequencenumber > 10 and sequencenumber <= 20

SQL Server 2012有更簡潔的文法，例子代碼如下所示

SELECT *FROM CustomersORDER BY CustomerIDOFFSET 10 ROWSFETCH NEXT 10 ROWS ONLY;

依據客戶編碼排序，跳過前面10筆記錄，取第10筆記錄。這很像Linq中的Skip.Take，Linq文法例子如下

var customers=customerList.Skip(10).Take(10);

 

異常處理 Exception Handling

SQL Server 2005引入了類似於.NET語言的異常處理機制到T-SQL代碼中，請參考下面的例子

BEGIN TRY    BEGIN TRANSACTION – Start the transaction    -- Delete the Customer    DELETE FROM Customers    WHERE EmployeeID = ‘CACTU’    -- Commit the change    COMMIT TRANSACTIONEND TRYBEGIN CATCH    -- There is an error    IF @@TRANCOUNT > 0        ROLLBACK TRANSACTION    -- Raise an error with the details of the exception    DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int    SELECT @ErrMsg = ERROR_MESSAGE(),        @ErrSeverity = ERROR_SEVERITY()    RAISERROR(@ErrMsg, @ErrSeverity, 1)END CATCH

如果在catch語句塊中捕獲了異常，只能引用RaiseError函數來繼續拋出異常。新版本的SQL Server引入了throw關鍵字，可取代RaiseError函數的作用。參考代碼如下

BEGIN TRY    BEGIN TRANSACTION -- Start the transaction    -- Delete the Customer    DELETE FROM Customers    WHERE EmployeeID = ‘CACTU’    -- Commit the change    COMMIT TRANSACTIONEND TRYBEGIN CATCH    -- There is an error    ROLLBACK TRANSACTION    -- Re throw the exception    THROWEND CATCH

異常處理機制的一個好處是N層復原(rollback)，拋出異常的程式，逐層向上尋找，直到找到處理異常的代碼。

 

預存程序執行改善 Execute Procedure Enhanced

在舊的SQL Server版本中，要返回一個查詢語句的列資訊，可以使用SET FMTONLY語句，它返回結果列，而不是實際的資料，請參考運行下面的語句：

SET FMTONLY ON;GOSELECT *    FROM  dbo.GBITEMGOSET FMTONLY OFF;

預存程序是一個先行編譯的批處理語句塊，先行編譯可改善效能，前一個版本的SQL Server應用關鍵字(WITH RECOMPILE) 可以強制重新編譯預存程序，產生新的執行計畫。新版本的SQL Server改善了查詢結果的返回資訊，可以對預存程序的查詢結果，進行別名字義。下面的代碼，重新定義預存程序的返回列資訊：

EXEC CustOrderDetail ‘2’WITH RESULT SETS(    (    ProductName1 varchar(100),    Unitprice1 varchar(100),    Quantity1 varchar(100),    Discount1 varchar(100),    ExtendedPrice1 varchar(100)    ));

Exec的參數With Results Set可以依據預存程序的實際返回結果，重新定義返回的列名或類型。參考下面的SQL語句：

CREATE PROCEDURE Denali_WithResultSetASBEGIN        SELECT 1 as No,’Tsql’ Type, ‘WithResultSet’ AS Feature UNION ALL       SELECT 2 as No,’Tsql’ Type, ‘Throw’ AS Feature UNION ALL       SELECT 3 as No,’Tsql’ Type, ‘Offset’ AS Feature UNION ALL       SELECT 4 as No,’Tsql’ Type, ‘Sequence’ AS Feature ENDGOEXEC Denali_WithResultSet WITH RESULT SETS(       (      No int,              FeatureType varchar(50),              FeatureName varchar(50)       )  ) 

上面的代碼示範了如何運用with result set來修改預存程序的返回列名稱。這個特性與第三方的工具整合，比如SSIS任務，報表中會有一定的改善作用。

 

中繼資料函數 Metadata Function

新版本的SQL Server增加了幾個預存程序用於擷取SQL Server 物件的中繼資料，比如下面的SQL語句：

EXEC sp_describe_first_result_set @tsql=N‘SELECT * FROM gbitem‘

它會返回表gbitem的每一列的中繼資料資訊，比如列名，是否可空，資料類型，排序等資料資訊。

下面的例子示範了如何應用上面提到的函數，返回預存程序的中繼資料：

CREATE PROC Production.TestProcASSELECT Name, ProductID, Color FROM Production.Product ;SELECT Name, SafetyStockLevel, SellStartDate FROM Production.Product ;GOSELECT * FROM sys.dm_exec_describe_first_result_set(‘Production.TestProc‘, NULL, 0) ;

此函數還可以返回多個SQL批處理查詢的中繼資料資訊，請參考下面的例子代碼

SELECT * FROM sys.dm_exec_describe_first_result_set(N‘SELECT CustomerID, TerritoryID, AccountNumber FROM Sales.Customer WHERE CustomerID = @CustomerID;SELECT * FROM Sales.SalesOrderHeader;‘,N‘@CustomerID int‘, 0) AS a;GO

如果一個預存程序想返回多筆記錄集，在舊版本的SQL Server中，只能擷取最後一次返回的記錄集。新版本的SQL Server對此作出一些改善，可以指定要返回的記錄集。

CREATE PROC TestProc2ASSELECT object_id, name FROM sys.objects ;SELECT name, schema_id, create_date FROM sys.objects ;GOSELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID(‘TestProc2‘), 0) ;SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID(‘TestProc2‘), 1) ;GO

這個特性會給程式處理上帶來很多便利，為返回二個結果集而不必定義二個重複的預存程序，而僅僅是返回的結果不同。

 

SQL 函數 SQL Function

新版本的SQL Server增加了很多函數，請參考園友的文章SQL Server 2012新增的內建函數嘗試

這些函數的到來，可以給SQL編程帶來便利性。不過，我以為自從SQL Server 2005引入了CLR，實現這些函數都相當容易，直接對.NET BCL一層簡單的封裝即可,不知道為何過了二個重要的版本後(SQL Server 2008,SQL Server 2008 R2)，才加入這些基礎函數。

 

關於SQL Server 2012 T-SQL方面更多的特性，請參考這裡：

http://dattatreysindol.com/2012/07/30/sql-server-2012-transact-sql-enhancements-learning-resources/

SQL Server 2012 T-SQL 新特性