SQL Server 2008對T-SQL語言的增強(qiáng)

Microsoft SQL Server 2008 對 T-SQL 語言進(jìn)行了?一步增強(qiáng)。為了讓開發(fā)人員盡快了解這些變化，我們針對 2007 年 6 月 CTP 版本的 SQL Server 2008 中的 T-SQL 語言的新增功能進(jìn)行了分析和嘗試。本文描述自 SQL Server 2008 CTP1 以來這些語言增強(qiáng)和變化。

　　本文包含如下內(nèi)容：

　　? T-SQL 行構(gòu)造器

　　? FORCESEEK 表提示

　　? GROUPING SETS

　　? 兼容性級別

　　? 用戶自定義表數(shù)據(jù)類型

　　? 表值參數(shù)

　　? MERGE 語句

　　本文適用于：Microsoft)R) SQL Server(TM) 2008 Developer Edition June 2007 CTP

　　1、T-SQL 行構(gòu)造器

　　T-SQL 行構(gòu)造器(Row Constructors)用來在 INSERT 語句中一次性插入多行數(shù)據(jù)。例如：

CREATE TABLE #a

(

Column1 nvarchar(max),

Column2 nvarchar(max)

);

GO

INSERT INTO #a

VALUES (

('1', '1'),

('2', '2')

);

select * from #a;

GO

drop TABLE #a;

GO

　　經(jīng)過增強(qiáng)后的 INSERT 語句的語法結(jié)構(gòu)如下。

[ WITH <common_table_expression> [ ,...n ] ]

INSERT

[ TOP ( expression ) [ PERCENT ] ]

[ INTO ]

{ <object> | rowset_function_limited

[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]

}

{

[ ( column_list ) ]

[ <OUTPUT Clause> ]

{ VALUES ( ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ] )

| derived_table

| execute_statement

| <dml_table_source>

| DEFAULT VALUES

}

}

[; ]

<object> ::=

{

[ server_name . database_name . schema_name .

| database_name .[ schema_name ] .

| schema_name .

]

table_or_view_name

}

<dml_table_source> ::=

select <select_list>

from ( <dml_statement_with_output_clause> )

[AS] table_alias [ ( column_alias [ ,...n ] ) ]

[ where <search_condition> ]

[ OPTION ( <query_hint> [ ,...n ] ) ]

2. FORCESEEK 提示

　　FORCESEEK 是一個(gè)新的表提示(Table Hints)，它用來指定 SQL Server 查詢優(yōu)化程序如何更高效的執(zhí)行查詢。該提示指示優(yōu)化程序?qū)Σ樵円玫谋砗鸵晥D通過索引檢索來作為唯一的查詢執(zhí)行訪問路徑。也就是強(qiáng)制通過索引檢索數(shù)據(jù)。例如：

USE tempdb;

GO

drop TABLE t;

GO

CREATE TABLE t(i int UNIQUE, j int, vc varchar(100));

CREATE INDEX t_vc ON t(vc);

GO

DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int;

select * from t where i IN (@p1, @p2, @p3, @p4, @p5);

GO

DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int;

select * from t WITH (FORCESEEK) where i IN (@p1, @p2, @p3, @p4, @p5);

GO

select * from t where vc LIKE 'Test%';

GO

select * from t WITH (FORCESEEK) where vc LIKE 'Test%';

GO

DECLARE @vc varchar(100);

select * from t where vc LIKE @vc;

GO

DECLARE @vc varchar(100);

select * from t WITH (FORCESEEK) where vc like @vc;

GO

　3. GROUPING SETS

　　新的 T-SQL 對 GROUP BY 子句增加了 GROUPING SETS, ROLLUP 和 CUBE 操作符。還有一個(gè)新的函數(shù) GROUPING_ID()，它相比 GROUPING() 函數(shù)返回更多分組級別的信息。WITH ROLLUP, WITH CUBE 和 ALL 等非 ISO 標(biāo)準(zhǔn)語法已經(jīng)不再有效。

　　4. 兼容性級別

　　新的 ALTER DATABASE SET COMPATIBILITY_LEVEL 語法替換了 sp_dbcomplevel 存儲(chǔ)過程。它用來設(shè)置特定數(shù)據(jù)庫的兼容性級別。其語法形式為：

ALTER DATABASE database_name

SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

您可以為 database_name 為名稱的數(shù)據(jù)庫設(shè)置特定兼容性級別。80、90 和 100 分別代表 SQL Server 2000、SQL Server 2005 和 SQL Server 2008。

      5. 用戶自定義表數(shù)據(jù)類型

　　現(xiàn)在，SQL Server 提供一種新的用戶自定義數(shù)據(jù)類型――自定義表數(shù)據(jù)類型(User-defined Table Types)。它可以作為參數(shù)提供給語句、存儲(chǔ)過程或者函數(shù)。您還可以為它創(chuàng)建唯一約束和主鍵。

　　請使用 CREATE TYPE 語句創(chuàng)建這種數(shù)據(jù)類型。如：

USE AdventureWorks;

GO

/* Create a user-defined table type */

CREATE TYPE LocationTableType AS TABLE

( LocationName VARchar(50)

, CostRate INT );

GO

對于表類型，有如下約束：

　　1、用戶自?義表類型不能作為表的列或者結(jié)構(gòu)化用戶自定義類型的域。

　　2、基于用戶自定義表類型的別名類型。

　　3、不允許 NOT FOR REPLICATION 選項(xiàng)。

　　4、CHECK 約束需要一個(gè)計(jì)算列。

　　5、在計(jì)算列上的主鍵必須包含 NOT NULL 和 PERSISTED 約束。

　　6、不能在用戶自定義表類型上創(chuàng)建非簇索引。除非索引是創(chuàng)建 PRIMARY KEY 或 UNIQUE 約束的返回值。

　　7、不能指定 DEFAULT 值。

　　8、一旦用戶自定義?類型被創(chuàng)建，則它就無法更改。

　　9、如果沒有定義用戶自定義表類型上的計(jì)算列，則用戶自定義函數(shù)無法調(diào)用。

　　6. 表值參數(shù)

　　數(shù)據(jù)庫引擎現(xiàn)在支持一種新的參數(shù)類型來引用用戶自定義表類型(參考 5)。表值參數(shù)可以發(fā)送更多的 SQL Server 數(shù)據(jù)。

　　下面的示例展示了如何使用表值參數(shù)。

USE AdventureWorks;

GO

/* Create a table type. */

CREATE TYPE LocationTableType AS TABLE

( LocationName VARchar(50)

, CostRate INT );

GO

/* Create a procedure to receive data for the table-valued parameter. */

CREATE PROCEDURE usp_InsertProductionLocation

@TVP LocationTableType READONLY

AS

SET NOCOUNT ON

INSERT INTO [AdventureWorks].[Production].[Location]

([Name]

,[CostRate]

,[Availability]

,[ModifiedDate])

select *, 0, GETDATE()

from　@TVP;

GO

/* Declare a variable that references the type. */

DECLARE @LocationTVP

AS LocationTableType;

/* Add data to the table variable. */

INSERT INTO @LocationTVP (LocationName, CostRate)

select [Name], 0.00

from

[AdventureWorks].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */

EXEC usp_InsertProductionLocation @LocationTVP;

GO

7. MERGE 語句

　　這個(gè)新增的 Transaction SQL 語句在一個(gè)基于源數(shù)據(jù)連接結(jié)果集的目標(biāo)表上執(zhí)行 INSERT、update 和 delete 操作。該語法允許您將一個(gè)數(shù)據(jù)源連接到目標(biāo)表或視圖上。然后在連接后的結(jié)果集上執(zhí)行多種操作。

　　MERGE 的語法為：

[ WITH <common_table_expression> [,...n] ]

MERGE

[ TOP ( expression ) [ PERCENT ] ]

[ INTO ] target_table [ [ AS ] table_alias ]

[ WITH ( <merge_hint> ) ]

USING <table_source>

ON <search_condition>

[ WHEN MATCHED [ AND <search_condition> ]

THEN <merge_matched> ]

[ WHEN [TARGET] NOT MATCHED [ AND <search_condition> ]

THEN <merge_not_matched> ]

[ WHEN SOURCE NOT MATCHED [ AND <search_condition> ]

THEN <merge_ matched> ]

<output_clause>

[ OPTION ( <query_hint> [ ,...n ] ) ]　　

;

<merge_hint>::=

{ [ <table_hint_limited> [ ,...n ] ]

[ [ , ] INDEX ( index_val [ ,...n ] ) ] }

<table_source> ::=

{

table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]

[ WITH ( table_hint [ [ , ]...n ] ) ]

| rowset_function [ [ AS ] table_alias ]

[ ( bulk_column_alias [ ,...n ] ) ]

| user_defined_function [ [ AS ] table_alias ]

| OPENXML <openxml_clause>

| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]

| <joined_table>

| <pivoted_table>

| <unpivoted_table>

}

<merge_matched>::=

{ update SET <set_clause> | delete }

<set_clause>::=

{ column_name = { expression | DEFAULT | NULL }

| { udt_column_name. { { property_name = expression

| field_name = expression }

| method_name ( argument [ ,...n ] ) } }

| column_name { .WRITE ( expression , @Offset , @Length ) }

| @variable = expression

} [ ,...n ]

<merge_not_matched>::=

INSERT [ ( <column_list> ) ]

{ VALUES ( <values_list> )

| DEFAULT VALUES }

<output_clause>::=

{

[ OUTPUT <dml_select_list> INTO { @table_variable | output_table }

[ (column_list) ] ]

[ OUTPUT <dml_select_list> ]

}

<dml_select_list>::=

{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]

[ ,...n ]

<column_name> ::=

{ DELETED | INSERTED | from_table_name } . { * | column_name }

| $ACTION

示例：在一條 SQL 語句中使用 where 在一張表上執(zhí)行 update 和 delete 操作

USE AdventureWorks;

GO

MERGE Production.ProductInventory AS pi

USING (select ProductID, SUM(OrderQty) from Sales.SalesOrderDetail sod

JOIN Sales.SalesOrderHeader soh

ON sod.SalesOrderID = soh.SalesOrderID

AND soh.OrderDate = GETDATE()

GROUP BY ProductID) AS src (ProductID, OrderQty)

ON (pi.ProductID = src.ProductID)

WHEN MATCHED AND pi.Quantity - src.OrderQty <> 0

THEN update SET pi.Quantity = pi.Quantity - src.OrderQty

WHEN MATCHED AND pi.Quantity - src.OrderQty = 0

THEN DELETE;

這個(gè)示例是一個(gè)非常典型的銷售定貨庫存問題。這個(gè)示例很簡單，表達(dá)的意思就是：如果某一個(gè)產(chǎn)品產(chǎn)生了銷售定單數(shù)據(jù)，則將其對應(yīng)的產(chǎn)品庫存除去該銷售定單所產(chǎn)生的數(shù)量，如果當(dāng)前庫存數(shù)量與該銷售定單數(shù)量相同，則從庫存表中刪除該產(chǎn)品的庫存紀(jì)錄。

　　我們看到，利用 MERGE 語句可以將復(fù)雜的 SQL 語句簡化。它比起 IF、CASE 等更加靈活和強(qiáng)大。

　　結(jié)論

　　Microsoft SQL Server 2008 對事務(wù)性 SQL 語言做了一些增強(qiáng)，提高了查詢效率。使得 SQl　Server 成為大中型企業(yè)數(shù)據(jù)庫的首先產(chǎn)品。SQL Server 2008 將伴隨 Visual Studio 2008 一起發(fā)布，開發(fā)人員提前了解這些信息有助于在 SQL Server 的新版本發(fā)布后快速建立基于該版本的企業(yè)級應(yīng)用程序。