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
长度,则会在转换后的表达式右侧添加空格,以使十六进制数字的个数保持为偶数。
|
参考下面的示例代码:
–
转换二进制值
0×4E616d65
到一个字符值
SELECT CONVERT(char(8), 0×4E616d65,
0) AS ‘Style 0,
二
进制到字符
‘
–
下面的示例演示了
Style
为
1
的情况下
,
如何强行截断结果值。
–
产生的结果值由于包含字符
0x
,
所以被截断
SELECT CONVERT(char(8), 0×4E616d65,
1) AS ‘Style 1,
二
进制到字符
‘
–
下面的示例演示了
Style
为
2
的情况下,没有截断结果值。
–
这是因为
0x
字
符未包含在结果中
SELECT CONVERT(char(8), 0×4E616d65,
2) AS ‘Style 2,
二
进制到字符
‘
–
转换字符值
Name
到一个二进制值
SELECT CONVERT(binary(8), ‘Name’,
0) AS ‘Style 0,
字符
到二进制
‘
SELECT CONVERT(binary(4),
‘0×4E616D65′, 1) AS ‘Style 1,
字符到二进制
‘
SELECT CONVERT(binary(4),
‘4E616D65′, 2) AS ‘Style 2,
字符到二进制
‘
结果如下:
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
天。
参考下面的代码:
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
表中存在下列数据:
执行下面的查询语句:
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
中,
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;
结果集如下:
上面的语句等同于下面的
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
两个表中的数据如下图所示:
请注意,这两个表中有两个共有的产品
-
客户行:客户
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
这样的命名方式。
上面语句的输出结果如下:
再查询
FactBuyingHabits
表,可以看到被更新和插入后的结果,如下所示:
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)
分享到:
相关推荐
SQL Server 2008 Transact-SQL Recipes SQL Server 2008 Transact-SQL Recipes
Microsoft SQL Server 2008 對Transact-SQL 語言進行了進一步增強!这个是对SQL2008在T_SQL方面增强作了详细的介绍!
《SQL Server 数据库》Transact-SQL程序设计实验报告.pdf《SQL Server 数据库》Transact-SQL程序设计实验报告.pdf《SQL Server 数据库》Transact-SQL程序设计实验报告.pdf《SQL Server 数据库》Transact-SQL程序设计...
Writing Queries Using Microsoft SQL Server 2008 Transact-SQL
SQL Server 2012 Transact-SQL DML Reference
SqlServer实验二:数据查询和Transact-SQL基础.doc SqlServer实验二:数据查询和Transact-SQL基础.doc SqlServer实验二:数据查询和Transact-SQL基础.doc SqlServer实验二:数据查询和Transact-SQL基础.doc Sql...
Transact—SQL是结构化查询语言(SQL)的增强版本,与许多ANSI SQL标准兼容,Transact-SQL代码已成为SQL Server的核心。本书全面介绍了Transact—SQL,全书共21章,按照由浅入深的顺序,详细介绍了Transact-SQL...
Beginning Transact-SQL with SQL Server 2000 and 2005 byPaul TurleyandDan Wood Wrox Press 2006 (594 pages) ISBN:076457955X Prepare for the ever-increasing demands of programming....
Prepare for Microsoft Exam 70-761–and help demonstrate your real-world mastery of SQL Server 2016 Transact-SQL data management, queries, and database programming. Designed for experienced IT ...
SQL Server 2008 Transact SQL Receipe
Beginning Transact-SQL with SQL Server 2000 and 2005 byPaul TurleyandDan Wood Wrox Press 2006 (594 pages) ISBN:076457955X Prepare for the ever-increasing demands of programming. Beginning with an ...
Beginning Transact-SQL with SQL Server 2000 and 2005 byPaul TurleyandDan Wood Wrox Press 2006 (594 pages) ISBN:076457955X Prepare for the ever-increasing demands of programming. Beginning with an ...
Welcome and thank you for reading XML and JSON Recipes for SQL Server. In the modern world of information technology, keeping data stored and manipulated reliably and efficiently is one of the first ...
SQL Server数据库应用与开发:第04章 Transact-SQL语言基础.ppt
Transact-SQL 对使用 Microsoft® SQL Server™ 非常重要。与 SQL Server 通讯的所有应用程序都通过向服务器发送 Transact-SQL 语句来进行通讯,而与应用程序的用户界面无关。 Transact-SQL 由多种应用程序生成,...
Transact-SQL参考(SQL Server 2000) 计算机专业电子书。 Transact-SQL参考(SQL Server 2000).chm
SQL Server 2005 Beta 2 Transact-SQL 增强功能
第二讲SQL Server编程工具及简单Transact-SQL语言
Transact-SQL 概述 Transact-SQL 对使用 Microsoft® SQL Server™ 非常重要。与 SQL Server 通讯的所有应用程序都通过向服务器发送 Transact-SQL 语句来进行通讯,而与应用程序的用户界面无关。
This book expresses the author’s views and opinions. The information contained in this book is provided without any express, statutory, or implied warranties. Neither the authors, Microsoft ...