作者:张洪举 Microsoft MVP <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
应用于:SQL Server 2008
日期:2008/9/1
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, 字符到二进制'
结果如下:
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></path><lock aspectratio="t" v:ext="edit"></lock></shapetype><shape id="图片_x0020_22" style="VISIBILITY: visible; WIDTH: 362.25pt; HEIGHT: 161.25pt; mso-wrap-style: square" alt="1.jpg" type="#_x0000_t75" o:spid="_x0000_i1030"><imagedata o:title="1" src="file:///C:%5CUsers%5Czhj%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_image001.jpg"></imagedata></shape>
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天。
<shape id="图片_x0020_23" style="VISIBILITY: visible; WIDTH: 174.75pt; HEIGHT: 137.25pt; mso-wrap-style: square" alt="2.jpg" type="#_x0000_t75" o:spid="_x0000_i1029"><imagedata o:title="2" src="file:///C:%5CUsers%5Czhj%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_image002.jpg"></imagedata></shape><shape id="图片_x0020_0" style="VISIBILITY: visible; WIDTH: 174.75pt; HEIGHT: 138pt; mso-wrap-style: square" alt="3.jpg" type="#_x0000_t75" o:spid="_x0000_i1028"><imagedata o:title="3" src="file:///C:%5CUsers%5Czhj%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_image003.jpg"></imagedata></shape>
参考下面的代码:
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表中存在下列数据:
<shape id="图片_x0020_1" style="VISIBILITY: visible; WIDTH: 102.75pt; HEIGHT: 58.5pt; mso-wrap-style: square" alt="4.jpg" type="#_x0000_t75" o:spid="_x0000_i1027"><imagedata o:title="4" src="file:///C:%5CUsers%5Czhj%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_image004.jpg"></imagedata></shape>
执行下面的查询语句:
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的两行汇总。
<shape id="图片_x0020_2" style="VISIBILITY: visible; WIDTH: 234.75pt; HEIGHT: 111pt; mso-wrap-style: square" alt="5.jpg" type="#_x0000_t75" o:spid="_x0000_i1026"><imagedata o:title="5" src="file:///C:%5CUsers%5Czhj%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_image005.jpg"></imagedata></shape>
而在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;
结果集如下:
<shape id="图片_x0020_3" style="VISIBILITY: visible; WIDTH: 114pt; HEIGHT: 49.5pt; mso-wrap-style: square" alt="6.jpg" type="#_x0000_t75" o:spid="_x0000_i1025"><imagedata o:title="6" src="file:///C:%5CUsers%5Czhj%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_image006.jpg"></imagedata></shape>
上面的语句等同于下面的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