SQL2012里面一些很实用的功能
目录
最近开发部换了2012, 相比05真是有很多提升, 特别是多出了一些高效率的关键字
然后后台的大部分query都要重写是闹哪样闹哪样闹哪样(ーー゛)
Merge
Desc
类似如下的需求:
对数据表Table插入对应的数据Data
如果Table中存在,而Data中也存在的数据则将其激活(set archived = 0)
如果Table中不存在,而Data中存在的数据则插入Table
需求可以进行扩展:
如果Table中存在,而在Data中不存在的数据则从Table中删除(set archived = 1)
Merge
如果用旧版本的SQL可能需要各种Join,Exists,Insert,Update,Delete
但是实际上用一条含有Merge的关键字就可以实现这样的需求
用法可以参考MS的API1
Example
declare @MergeFrom as table(
a integer
)
declare @MergeTo as table(
a integer,
archived integer
)
insert into @MergeFrom values (2)
insert into @MergeFrom values (3)
insert into @MergeFrom values (4) /*will be inserted */
insert into @MergeTo values(1,0) /*will be archived */
insert into @MergeTo values(2,0)
insert into @MergeTo values(3,1) /*will be updated*/
/* Before merge */
select *
from @MergeTo
merge into @MergeTo as t
using @MergeFrom as f
on t.a = f.a
when matched
/* matched: these record are in use, set not archived */
then update set t.archived = 0
when not matched
/* record not matched in @MergeFrom: insert into @MergeFrom */
then insert values(f.a, 0)
when not matched by source
/*record not matched in @MergeFrom:*/
then delete;
/* after Merge */
select *
from @MergeTo- 这里对于
when之后的条件可以进行扩展, 比如添加AND或者其他条件等等 Merge后面也可以添加各种select的修饰符, 比如top等等
运行结果
merge 里面使用 output
一个 merge 语句可能非常复杂
因此偶尔我们需要将变化了的结果输出或者存放日志到其他地方
这时候可能就会用到 output
merge into pcc_attributeDeterm t
using zAssessResult_AllRequiredType f
on t.attributeID = f.attributeID
when matched
then update set
...
when not matched
then insert values(
...
)
output
'AttributeDeterm' RefType
, INSERTED.PCC_AttributeDetermID RefID
, CASE WHEN ($action = 'INSERT') THEN 'Created' ELSE 'Updated' END as ActionType
, '' ActionDesc
, 'System Determination' ActionBy
, INSERTED.SysDetermDate ActionDate
INTO dbo.ActionLog;- 不能使用
UPDATED关键字, 修改的数据全部都出现在INSERTED里面 - 只能同时出现一个
OUTPUT + INTO, 全部语句结尾还能再OUTPUT但是这个OUTPUT不能带有INTO
也就是:
output
xxx
INTO dbo.ActionLog
output
xxx
--INTO dbo.ActionLog2; -- 不能同时使用俩INTOlimit & offset
这个分页功能其实很早就有需求了, 也不知道为何到2012才引入这个关键字
以前在DB端进行分页需要首先筛选出所有列, 然后根据index进行二次筛选
当然这儿的第一步筛选所有列就已经很影响效率, offset就可以解决这个问题
Example2
用法及其简单, 放在Order后方即可
SELECT First Name + ' ' + Last Name
FROM Employees
ORDER BY First Name
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;