DELETE语句

--建表 select * into distribution from sys.objects                                 --1.当delete语句要关联其他表时与update语句类似，可参考上面update语句的写法  --2.truncate table语句删除行比delete快很多，不过必须一次删除所有的行(没有where子句) --之所以快是因为记录的日志很少，采用表级别锁。 --如果表中有IDENTITY列，会被重置为列定义的种子值4、TOP--1.在一个事务中删除所有记录，此表的记录有1000w条 delete from distribution  --2.通过top每次只删除1000条记录 while (select COUNT(*) from distribution) > 0 begin 	delete top (1000) 	from distribution end  /*=============================================================== 比较1和2(不只限于delete，还包括update、insert),2有以下优点：  1.每次操作1000条，就提交一次，那么产生少量的日志，使日志空间更容易被重用;   如果一次删除大量记录，而产生的大量日志可能比整个日志文件还大，   那么会引起日志文件的自动增长，会影响性能    2.分块操作记录，一次锁住更少的记录，占用更少的锁资源，   锁定时间更短，操作完成后这些记录可被其他进程访问，并发性更好 =================================================================*/



OUTPUT子句 

create table t(vid int not null,pic varchar(10) not null)  insert into t values(1,'abc'),       (2,'def'),       (3,'hjkl')  --output必须写在where子句之前 update t set pic = 'xyz'       --更新操作由删除+添加组合的 output deleted.vid,   --删除的记录        deleted.pic,                inserted.vid,  --添加的记录        inserted.pic where vid < 100  --output写在values之前 insert into t(vid,pic) output inserted.* values(5,'mn')   declare @temp table(vid int,pic varchar(10))  delete from t output deleted.vid,            --引用所有字段deleted.*        deleted.pic into @temp where vid < 100


output子句的一个应用，由于主表和附表是级联删除的，需要实现删除主表记录时，自动保存主表和附表中相关重要字段的值：

  --创建主表 create table t1(id int primary key,v varchar(10))  --创建附表，级联删除 create table t2 ( idd int, id int foreign key references t1(id) on delete cascade, vv varchar(20) )  insert into t1 select 1,'a' union all select 2,'b'   insert into t2 select 1,1,'www' union all select 1,2,'csdn'   --创建存储删除的t1表的字段 create table temp_t1_delete(id int,v varchar(10))  --创建存储删除的t2表的字段 create table temp_t2_delete(id int,vv varchar(20))   go  --创建表t2的delete触发器 create trigger dbo.trigger_t2_delete  on dbo.t2 for delete as begin    insert into temp_t2_delete(id,vv)    select id,vv    from deleted end go   --删除主表记录，自动把删除的主表记录，保存在temp_t1_deletei表中   delete from t1   output deleted.id,            --引用所有字段deleted.*          deleted.v into temp_t1_delete where id = 1     --查询已删除的记录 select * from temp_t1_delete t1 left join temp_t2_delete t2        on t1.id = t2.id /* id	v	id	vv 1	a	1	www */


MERGE语句

create table t_org(org_id int,                    v1 varchar(20),                    v2 varchar(30));                    insert into t_org select 1,'org1','' union all select 2,'org2','name2' union all select 3,'org3','name3' union all select 4,'org4','name4' union all select 5,'org5','name5'   create table t_store(org_id int,                      v1 varchar(20),                      v2 varchar(30)); insert into t_store select 1,'org1','' union all select 2,'org2-t','name2-t' union all select 3,'org3-t','name3-t' union all select 4,'org4-t','name4-t' union all select 5,'org5-t','name5-t' union all select 6,'org6-t','name6-t' union all select 7,'org7-t','name7-t'  --生成临时表 select * into #t_org from t_org select * into #t_store from t_store  --定义表变量 declare @delete_insert_t_org table(            change nvarchar(100),                org_id int,v1 varchar(20),v2 varchar(30),        --删除的            org_id_t int,v1_t varchar(20),v2_t varchar(30))  --添加的   ;with mm   --作为merge语句中using的内部派生表 as (      select m.org_id,             m.v1,             m.v2      from #t_store m      where m.org_id >1 )   --注意：表 with(tablock)，另外通过top关键字只是处理3条记录 merge top (3) into #t_org with (tablock) as b   using (          select *          from mm with (tablock)  --引用上面CTE公用表表达式产生的内部派生表       ) m     on m.org_id = b.org_id      --为了区分是否需要修改，可以增加一个字段来区分，                                --但是这个字段不应该作为关联条件，                                --因为会导致接下来运行的merge分块语句把刚才目标表中update过的那条记录，                                --重复插入目标表中，而是写在when的条件中                                     when matched and b.v1 <> m.v1 and isnumeric(m.org_id) = 1  --可以在这里写：区分字段过滤条件      then update set v1 = m.v1,v2 = m.v2   when not matched by target  --目标表中没有      then insert (org_id,v1,v2) values(m.org_id,m.v1,m.v2)  --不可通过values关键字一次添加多列            when not matched by source  --源表中没有      then delete   output  $action,            --操作：delete、insert、update         inserted.org_id,         inserted.v1,         inserted.v2  ,      --可改为inserted.*                          deleted.org_id,         deleted.v1,         deleted.v2          --可改为deleted.*                                      INTO @delete_insert_t_org   --output的输出放入表变量中  --关联提示         option (loop join);    --注意：merge必须以分号结尾          select * from @delete_insert_t_org