WEB开发网
开发学院数据库MSSQL Server SQL Server 2008 的 Transact-SQL 语言增强 阅读

SQL Server 2008 的 Transact-SQL 语言增强

 2008-09-06 10:01:33 来源:WEB开发网   
核心提示:Microsoft SQL Server 2008 对 Transact-SQL 语言进行了进一步增强,主要包括:ALTER DATABASE 兼容级别设置、复合运算符、CONVERT 函数、日期和时间功能、GROUPING SETS、MERGE 语句、SQL 依赖关系报告、表值参数和 Transact-SQL 行构造

Microsoft SQL Server 2008 对 Transact-SQL 语言进行了进一步增强,主要包括:ALTER DATABASE 兼容级别设置、复合运算符、CONVERT 函数、日期和时间功能、GROUPING SETS、MERGE 语句、SQL 依赖关系报告、表值参数和 Transact-SQL 行构造函数。

1.ALTER DATABASE 兼容级别设置

某些数据库行为与 SQL Server 版本有关,通过 ALTER DATABASE 下面新增的语法,可以设置数据库兼容级别,它取代了以前版本中的 sp_dbcmptlevel 过程。

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

可用的设置值80、90、100分别代表 SQL Server 2000、2005和2008。

2.复合运算符

SQL Server 2008 现在支持如下复合运算符,可执行操作并将变量设置为结果。

运算符操作
+=将原始值加上一定的量,并将原始值设置为结果
-=将原始值减去一定的量,并将原始值设置为结果
*=将原始值乘上一定的量,并将原始值设置为结果
/=将原始值除以一定的量,并将原始值设置为结果
%=将原始值除以一定的量,并将原始值设置为余数
&=对原始值执行位与运算,并将原始值设置为结果
^=对原始值执行位异或运算,并将原始值设置为结果
|=对原始值执行位或运算,并将原始值设置为结果

如:

DECLARE @x1 int = 27;
SET @x1 += 2 ;
SELECT @x1 -- 返回29

3.CONVERT 函数

CONVERT 函数现在允许在二进制和字符十六进制值之间进行转换。函数语法格式如下:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

expression 是被转换的有效的表达式,data_type 目标数据类型(不能使用别名数据类型),length 指定目标数据类型长度的可选整数,style 指定 CONVERT 函数如何转换 expression 的整数表达式。

如果 expression 为 binary(n)、varbinary(n)、char(n) 或 varchar(n),则 style 可以为下表中显示的值之一。

输出
0(默认值)将 ASCII 字符转换为二进制字节,或者将二进制字节转换为 ASCII 字符。每个字符或字节按照 1:1 进行转换。

如果 data_type 为二进制类型,则会在结果左侧添加字符 0x。

1, 2对于 style 1,将在转换后的结果左侧添加字符 0x。作为要转换的二进制表达式,字符 0x 必须为表达式中的前两个字符。

在style为2的情况下,生成的二进制值不会包含字符 0x。作为要转换的二进制表达式,也不需要在字符前面包含字符 0x。

如果 data_type 为二进制类型,则表达式必须为字符表达式。

如果转换后的表达式长度大于 data_type 长度,则会在右侧截断结果。

如果固定长度 data_types 大于转换后的结果,则会在结果右侧添加零。

如果 data_type 为字符类型,则表达式必须为二进制表达式。每个二进制字符均转换为两个十六进制字符。如果转换后的表达式长度大于 data_type 长度,则会在右侧截断结果。

如果 data_type 为固定大小的字符类型,并且转换后的结果长度小于其 data_type 长度,则会在转换后的表达式右侧添加空格,以使十六进制数字的个数保持为偶数。

参考下面的示例代码:

--转换二进制值 0x4E616d65 到一个字符值
SELECT CONVERT(char(8), 0x4E616d65, 0) AS 'Style 0, 二进制到字符'
--下面的示例演示了 Style 为 1 的情况下,如何强行截断结果值。
--产生的结果值由于包含字符 0x ,所以被截断
SELECT CONVERT(char(8), 0x4E616d65, 1) AS 'Style 1, 二进制到字符'
--下面的示例演示了 Style 为 2 的情况下,没有截断结果值。
--这是因为 0x 字符未包含在结果中
SELECT CONVERT(char(8), 0x4E616d65, 2) AS 'Style 2, 二进制到字符'
--转换字符值 Name 到一个二进制值
SELECT CONVERT(binary(8), 'Name', 0) AS 'Style 0, 字符到二进制'
SELECT CONVERT(binary(4), '0x4E616D65', 1) AS 'Style 1, 字符到二进制'
SELECT CONVERT(binary(4), '4E616D65', 2) AS 'Style 2, 字符到二进制'

结果如下:

SQL Server 2008 的 Transact-SQL 语言增强

4.日期和时间功能

DATEPART ( datepart , date )函数用于返回 date中的指定 datepart 的整数。如:

SELECT DATEPART(YEAR,'2007-05-10') --返回2007

SQL Server 2008 包含对 ISO 周-日期系统的支持,即周的编号系统。每周都与该周内星期四所在的年份关联。例如,2004 年第 1 周 (2004W01) 从 2003 年 12 月 29 日星期一到 2004 年 1 月 4 日星期天。一年中最大的周数可能为 52 或 53。这种编号方式通常用于欧洲国家,但其他国家/地区很少用到。

下面分别是2010年和2009年1月份的日历。由于2010年第一个星期中的星期四是2010-1-7日,所以2010-1-3日及之前的日期会作为2009年的第53个星期,而不是2010年的第一个星期。而对于2009年1月份的日历,由于星期四是2009-1-1,所以该星期会作为2009年的第一个星期。当然,该星期也包含了2008-12-28至31的4天。

SQL Server 2008 的 Transact-SQL 语言增强SQL Server 2008 的 Transact-SQL 语言增强

参考下面的代码:

SELECT DATEPART(ISO_WEEK,'2010-1-3') --返回53
SELECT DATEPART(ISO_WEEK,'2010-1-4') --返回1
SELECT DATEPART(ISO_WEEK,'2009-1-1') --返回1

5.ROLLUP、CUBE 和GROUPING SETS

在SQL Server 2008之前,进行分组统计汇总,可以在GROUP BY子句中使用WITH ROLLUP和WITH CUBE参数。ROLLUP指定在结果集内不仅包含由GROUP BY提供的行,还包含汇总行。按层次结构顺序,从组内的最低级别到最高级别汇总组。而CUBE参数则在使用ROLLUP参数所返回结果集的基础上,再将每个可能的组和子组组合在结果集内返回。

例如,假设dbo.T1表中存在下列数据:

SQL Server 2008 的 Transact-SQL 语言增强

执行下面的查询语句:

SELECT CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY CustName,ProductID
WITH CUBE
ORDER BY CustName,ProductID;
  
SELECT CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY CustName,ProductID
WITH ROLLUP
ORDER BY CustName,ProductID;

得到下面的结果集合,可以看出,使用WITH CUBE多出了对子组ProductID的两行汇总。

SQL Server 2008 的 Transact-SQL 语言增强

而在SQL Server 2008中,GROUPING SETS、ROLLUP 和 CUBE 运算符已添加到 GROUP BY 子句中。不再推荐使用不符合 ISO 的 WITH ROLLUP、WITH CUBE 和 ALL 语法。在SQL Server 2008中,可以将上面的WITH CUBE语句改写为如下的形式:

SELECT CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY CUBE(CustName,ProductID)
ORDER BY CustName,ProductID;

如果不需要获得由完备的 ROLLUP 或 CUBE 运算符生成的全部分组,则可以使用 GROUPING SETS 仅指定所需的分组。例如,下面的语句将得到分别按CustName和ProductID分组汇总结果集的并集。

SELECT CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY GROUPING SETS(CustName,ProductID)
ORDER BY CustName,ProductID;

结果集如下:

SQL Server 2008 的 Transact-SQL 语言增强

上面的语句等同于下面的UNION ALL语句:

SELECT CustName,NULL AS ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY CustName
UNION ALL
SELECT NULL AS CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY ProductID

6.MERGE 语句

在 SQL Server 2008 中,可以使用 MERGE 语句在一条语句中根据与源表联接的结果对目标表执行 INSERT、UPDATE 或 DELETE 操作。如:使用一个语句有条件地在单个目标表中插入或更新行,如果目标表中存在相应行,则更新一个或多个列;否则,会将数据插入新行。使用该语句还可以同步两个表,根据与源数据的差别在目标表中插入、更新或删除行。

MERGE 语法包括如下五个主要子句:

MERGE 子句用于指定作为插入、更新或删除操作目标的表或视图。

USING 子句用于指定要与目标联接的数据源。

ON 子句用于指定决定目标与源的匹配位置的联接条件。

WHEN 子句用于根据 ON 子句的结果指定要执行的操作。

OUTPUT 子句针对更新、插入或删除的目标对象中的每一行返回一行。

其完整的语法格式如下:

[ WITH <common_table_expression> [,...n] ]
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source>
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
      THEN <merge_matched> ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
      THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
      THEN <merge_matched> ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]

使用下面的语句创建两个表:

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Purchases', N'U') IS NOT NULL
  DROP TABLE dbo.Purchases;
GO
CREATE TABLE dbo.Purchases (
  ProductID int, CustomerID int, PurchaseDate datetime,
  CONSTRAINT PK_PurchProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.Purchases VALUES(707, 11794, '20060821'),
(707, 15160, '20060825'),(708, 18529, '20060821'),
(712, 19072, '20060821'),(870, 15160, '20060823'),
(870, 11927, '20060824'),(870, 18749, '20060825');
GO
IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL
  DROP TABLE dbo.FactBuyingHabits;
GO
CREATE TABLE dbo.FactBuyingHabits (
  ProductID int, CustomerID int, LastPurchaseDate datetime,
  CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.FactBuyingHabits VALUES(707, 11794, '20060814'),
(707, 18178, '20060818'),(864, 14114, '20060818'),
(870, 17151, '20060818'),(870, 15160, '20060817'),
(871, 21717, '20060817'),(871, 21163, '20060815'),
(871, 13350, '20060815'),(873, 23381, '20060815');
GO

两个表中的数据如下图所示:

SQL Server 2008 的 Transact-SQL 语言增强

请注意,这两个表中有两个共有的产品-客户行:客户 11794 购买了产品 707,客户 15160 购买了产品 870。对于这些行,可以使用 WHEN MATCHED THEN 子句利用 Purchases 中这些购买记录的日期来更新 FactBuyingHabits。我们可以使用 WHEN NOT MATCHED THEN 子句将所有其他行插入 FactBuyingHabits。参考下面的语句:

MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
  UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
  INSERT (CustomerID, ProductID, LastPurchaseDate)
  VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*;

$action用于在 OUTPUT 子句中指定一个 nvarchar(10) 类型的列,列的值是代表所执行操作的INSERT、UPDATE或DELETE。Inserted.*和Deleted.*分别用于指定返回所有插入行的列和删除行的列。如果要指定具体的列,可以使用Inserted.ProductID这样的命名方式。

上面语句的输出结果如下:

SQL Server 2008 的 Transact-SQL 语言增强

再查询FactBuyingHabits表,可以看到被更新和插入后的结果,如下所示:

SQL Server 2008 的 Transact-SQL 语言增强

7.SQL 依赖关系报告

SQL Server 2008 引入了新的目录视图和系统函数用以提供一致可靠的 SQL 依赖关系报告。所谓依赖关系,通俗的讲:存储过程1需要使用存储过程2提供的结果,它们之间就是一种依赖关系。可以使用 sys.sql_expression_dependencies、sys.dm_sql_referencing_entities 和 sys.dm_sql_referenced_entities 来报告架构绑定和非架构绑定对象的跨服务器、跨数据库和数据库 SQL 依赖关系。

下例将创建一个表、一个视图和三个存储过程。这些对象将用在后面的查询中以演示如何报告依赖关系信息。可看到 MyView 和 MyProc3 均引用 Mytable。MyProc1 引用 MyView,而 MyProc2 引用 MyProc1。

USE AdventureWorks;
GO
-- Create entities
CREATE TABLE dbo.MyTable (c1 int, c2 varchar(32));
GO
CREATE VIEW dbo.MyView
AS SELECT c1, c2 FROM dbo.MyTable;
GO
CREATE PROC dbo.MyProc1
AS SELECT c1 FROM dbo.MyView;
GO
CREATE PROC dbo.MyProc2
AS EXEC dbo.MyProc1;
GO
CREATE PROC dbo.MyProc3
AS SELECT * FROM AdventureWorks.dbo.MyTable;
  EXEC dbo.MyProc2;
GO

下面的示例查询 sys.sql_expression_dependencies 目录视图以返回由 MyProc3 引用的实体。

USE AdventureWorks;
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name
  ,referenced_server_name AS server_name
  ,referenced_database_name AS database_name
  ,referenced_schema_name AS schema_name
  , referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'dbo.MyProc3');
GO

下面是结果集:

referencing_entity server_name database_name schema_name referenced_entity
------------------  -----------  -------------   ----------- --  ---------------
MyProc3      NULL    NULL      dbo     MyProc2
MyProc3      NULL    AdventureWorks  dbo     MyTable

上面的查询返回了两个在 MyProc3 定义中按名称引用的实体。服务器名称为 NULL,因为被引用实体没有使用有效的由四部分组成的名称指定。返回的结果中显示了 MyTable 的数据库名称,因为在存储过程中是使用由三部分组成的有效名称定义此实体的。

8.表值参数

数据库引擎引入了可以引用用户定义表类型的新参数类型。表值参数可以将多个数据行发送到 SQL Server 语句或例程(比如存储过程或函数),而不用创建临时表。表值参数具有更高的灵活性,在某些情况下,可比临时表或其他传递参数列表的方法提供更好的性能。表值参数具有以下优势:

首次从客户端填充数据时,不获取锁。

提供简单的编程模型。

允许在单个例程中包括复杂的业务逻辑。

减少到服务器的往返。

可以具有不同基数的表结构。

是强类型。

使客户端可以指定排序顺序和唯一键。

与其他参数一样,表值参数的作用域也是存储过程、函数或动态 Transact-SQL 文本。同样,表类型变量也与使用 DECLARE 语句创建的其他任何局部变量一样具有作用域。

与BULK INSERT操作相比,频繁使用表值参数将比大型数据集要快。大容量操作的启动开销比表值参数大,与之相比,表值参数在插入数目少于 1000 的行时具有很好的执行性能。

下面是SQL Server帮助中的示例,演示了如何执行以下操作:创建表值参数类型,声明变量来引用它,填充参数列表,然后将值传递到存储过程。

USE AdventureWorks;
GO
  
/* 创建一个table类型 */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO
  
/* 创建一个存储过程,用于从表值参数接收数据 */
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 @LocationTVP
AS LocationTableType;
  
/* 添加数据到表值变量 */
INSERT INTO @LocationTVP (LocationName, CostRate)
  SELECT [Name], 0.00
  FROM
  [AdventureWorks].[Person].[StateProvince];
  
/* 传递表值变量数据给存储过程 */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

9.Transact-SQL 行构造函数

增强后的 Transact-SQL 可以允许将多个值插入单个 INSERT 语句中,语法比较简单。参考下面的代码:
/* 创建一个表 */
CREATE TABLE dbo.T1(
CustName char(20) ,
ProductID int ,
MadeFrom char(20) ,
Sales numeric(20, 2)
)
  
/* 插入2行数据 */
INSERT INTO dbo.T1
VALUES ('Jane',1,'China',20.00),
    ('Jack',2,'USA',10.00)

Tags:SQL Server Transact

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接