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 的 API¹

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; -- 不能同时使用俩 INTO

limit & offset

这个分页功能其实很早就有需求了, 也不知道为何到 2012 才引入这个关键字

以前在 DB 端进行分页需要首先筛选出所有列, 然后根据 index 进行二次筛选

当然这儿的第一步筛选所有列就已经很影响效率, offset就可以解决这个问题

Example²

用法及其简单, 放在Order后方即可

SELECT First Name + ' ' + Last Name 
FROM Employees 
ORDER BY First Name 
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

参考文献

Footnotes

1. MERGE (Transact-SQL) ↩

2. OFFSET FETCH 子句 (SQL Server Compact) ↩