SQL SERVER 2005 新增语法

1.表的同义名(Synonyms)

为表创建同义名,这样就可以在Select语句里边使用表的同义名了.注意在更改表的定义的时候只能使用表的原名,而不能使用表的同义名.

--创建同义名

CREATE SYNONYM syn_OR
FOR dbo.Orders

--使用同义名查询表

select * from syn_OR

--删除同义名

DROP SYNONYM syn_OR

2.INTERSECT 操作符

INTERSECT操作符就是取表集A和表集B的交集.

--查询,Northwind数据库

select * from orders
intersect
select * from orders
where customerid='vinet'

3.varchar(max),nvarchar(max),varbinary(max)数据类型.

还记得在大文本的时候,我们被迫使用text数据类型带来的不便吗?这回好了,2005带来的新的数据类型兼容以前的varchar数据类型,但是存储的空间更大了.varchar(max)和varbinary(max)可以存储2^31,而nvarchar(max)可以存储2^30字节,够用了吧!

-- 创建测试表

create table #t(id int,Study_Varcharmax varchar(max))

-- 插入数据
insert #t select 1,'Imagine this is a very long non-Unicode string'
insert #t select 2,'Imagine this is another very long non-Unicode string'

-- 使用.write语法来进行更新

update #t
set Study_Varcharmax .write('n incredibly', 17, 5)
where id = 1

-- 查询结果.
select * from #t
当然varchar(max)完全兼容以前的varchar数据类型,比如你可以在游标的fetch语句取得它的值,使用.write更新,在触发器的inserted或者deleted里边引用,还可以使用字符串函数如,len,substr等.

4.Top后边的参数.

还记得Top后边不能使用参数给我们带来的不便吗?2005终于可以在Top后边使用参数了,快来和我一起试验吧!

DECLARE @n int
SET @n = 10

SELECT TOP(@n) *
FROM Products
ORDER BY UnitPrice DESC

5.获取DML语句所影响的行.

以前我们只能通过触发器来获取删除或者更新语句所影响的行的信息,现在我们只要使用新的Output语法,就能轻松的获取DML语句所影响的行的信息.

-- 创建测试表,并插入测试数据

create table #t(id int,name varchar(200))

insert #t select 1,'first'
insert #t select 2,'second'

-- 使用表变量来保存输出结果集.

declare @tb_t table(id int,name varchar(200))

update #t set name='abc'
output inserted.id,inserted.name into @tb_t
where id=1

select * from @tb_t

6.新增的Some,Any,All运算符.

语法:<scalar_expression> { = | <> | != | > | >= | !> | < | <= | !<}
        {SOME | ANY} {subquery}
子查询返回一个结果集,scalar_expression和结果集任何一个比较成功,就返回True.

<scalar_expression> { = | <> | != | > | >= | !> | < | <= | !<}
        All {subquery}

自查询返回一个结果集,scalar_expression只有和结果集所有比较成功,才返回True.

刚接触这个运算符,感觉和in比较类似,而实际上如果合理运用,这个运算符的功能是非常强大的.

select *
from Products
where CategoryID=any(select categoryid
                     from dbo.Categories
                     where categoryName in ('Produce','Seafood'))

7.PIVOT and UNPIVOT

PIVOT and UNPIVOT的功能相信大家都已经熟悉了,我们结束了用拼凑SQL字符串的方式来进行行列转换的时代.但是Pivot的功能不只如此,我觉得它最大的功能是能让我们灵活的对数据表进行聚集分析,就像OLAP多维数据集那样.

-- Northwind的Order表:统计每个雇员在1996,1997,1998的定单数量.

select EmployeeID,[1996] as Y_1996,[1997] as Y_1997,[1998] as Y_1998
from
(
select EmployeeID,Year(OrderDate) as Year_Order,OrderID
from orders
) t
pivot
(count(OrderID) for Year_Order in ([1996],[1997],[1998])) pvt
order by EmployeeID

8.表值函数增强功能.

以前我们使用表值函数,只能用select * from 表值函数 的形式,如果我们想和别的表连接就做不到了.现在2005新增的{CROSS | OUTER} APPLY运算符可以让我们轻松的做到这一点.下边我就以一个例子来说明.

表1(t):sail cmd
9258 LBQR|LB
92587 A|19|1

想变成如下的形式:

sail cmd
9258 LBQR
9258 LB
92587 A
92587　19
92587　1
-- 步骤1:表值函数

create function dbo.fn_t(@sail int,@cmd varchar(1000))
returns @tb_t table(sail int,cmd varchar(1000))
as
begin
        while(charindex('|',@cmd)>0) -- 循环取得列表值,并插入@tb_t表里边.
        begin
                insert @tb_t select @sail,left(@cmd,charindex('|',@cmd)-1)
                set @cmd=stuff(@cmd,1,charindex('|',@cmd),'')
        end
        insert @tb_t select @sail,@cmd -- 插入最后一个值
return
end
Go

-- 步骤2:查询

select t.sail,b.cmd
from t
    cross apply dbo.fn_t(t.sail,t.cmd) b
order by t.sail

9.新的Ranking函数.

a.Row_Number()函数

-- Northwind表：对顾客的City进行排名

select Row_Number() over(Order By City) as RowID,*
from Customers

-- Northwind表:对国家内的城市进行排名

select Row_Number() over(Partition by Country Order By City),*
from Customers
b.Dense_Rank()函数

总的来说，Dense_Rank()函数和Row_Number()函数的功能是类似的，只不过在排名结果上略有不同．在有相同的值的时候，Dense_Rank()排名是连续的，而Row_Number()排名是跳跃的．

10.错误处理.(try catch)

还记得我们在SQL脚本里边使用大量的if @@error>0这样的语句来进行错误检测吗?现在好了,2005的try catch语法可以让我们轻松的进行错误检测.注意try catch 不能检测严重级小于等于10或者的错误,因为这样的错误只是一些警告信息.也不能检测严重级别大于20的的错误,因为这样的错误会终止数据库引擎的工作.

-- 创建测试表

create table #t(id int identity(1,1),name varchar(100) not null)

-- 执行脚本
begin try
   insert #t(name) select Null --插入空值,违反了非空的约束条件.
end try
begin catch
   select ERROR_NUMBER() ErrorNumber,
          ERROR_STATE() ErrorState,
          ERROR_SEVERITY() ErrorSeverity,
          ERROR_MESSAGE() ErrorMessage
end catch
11.DDL触发器.

我记得以前我们总是为一个表的突然失踪而苦恼,不知道这个表被谁给删除了.现在好了,使用DDL触发器,我们可以追踪表的去向了.

-- 测试表

create table t(id int identity(1,1),name varchar(100) not null)

-- 日志记录表.记住详细信息是存储在xml类型中.

create table DB_Log(F_LogID int identity(1,1),F_Content xml not null)

-- 创建触发器.

create trigger tri_t
on database
for create_table,drop_table
as
insert into DB_Log values(EventData())
Go

--删除表

drop table t

select * from DB_Log

-- 追踪信息.看起来信息非常详细的.足够我们把"凶手"追查出来的.

<EVENT_INSTANCE>
<EventType>DROP_TABLE</EventType>
<PostTime>2006-05-13T09:11:15.767</PostTime>
<SPID>54</SPID>
<ServerName>LILY/STUDY2005</ServerName>
<LoginName>LILY/Administrator</LoginName>
<UserName>dbo</UserName>
<DatabaseName>Northwind</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>t</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>drop table t
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>

12.使用OpenRowSet来读取数据文件.

以前我们只能用OpenRowSet来读取别的数据源的数据,但是对于数据文件的读取就不那边方便了.新的OpenRowSet语法增加了Bulk功能,可以让我们直接从数据文件里边读取数据.

-- 生成用于测试的数据文件和格式文件.(以Northwind的Customers表为例)

Bcp Northwind.dbo.Customers out D:/Customer.txt -T -c

Bcp Northwind.dbo.Customers format nul -f D:/Customer.fmt -T -c

-- 使用OpenRowSet来读取刚才生成的数据文件.

select *
from OpenRowSet(Bulk N'D:/Customer.txt',FormatFile=N'D:/Customer.fmt') as t

13.外连接的另类语法.

以前内连接我们可以使用where来代替,但是外连接就没有这样的语法了.2005新推出的*=和=*让我们做到了这点.

-- Northwind

select a.*,b.*
from Products a,Categories b
where a.CategoryID*=b.CategoryID

----------------------

PS：？第13条，也就是最后一条，*=和=* 是SQL2000大力提倡抛弃的语法吧（SQL2000连语法帮助都不提供），现在还可以用应该只是保留兼容性而已。（参考SQL2000帮助文档中的*=和=*，如下）

Transact-SQL 联接
在早期的 Microsoft® SQL Server™ 2000 版本中，使用 *= 和 =* 在 WHERE 子句中指定左、右外部联接条件。有时，该语法会导致有多种解释的不明确查询。FROM 子句中指定遵从 SQL-92 的外部联接，不会导致上述不确定性。因为 SQL-92 语法更为精确，所以，本版中未包括有关在 WHERE 子句中使用旧的 Transact-SQL 外部联接语法的详细信息。以后的 SQL Server 版本可能不再支持该语法。任何使用 Transact-SQL 外部联接的语句都应改为使用 SQL-92 语法。

SQL-92 标准支持 FROM 或 WHERE 子句中的内部联接规范。WHERE 子句中指定的内部联接不会出现与 Transact-SQL 外部联接语法相同的不确定性问题。

而且在新版的SQL联机丛书中，=*已经没有，*=已经解释为.net语法 乘等于。