一、写在前面 - 想说爱你不容易
为了升级数据库至sql server 2008 r2，拿了一台现有的pc做测试，数据库从正式库restore(3个数据库大小夸张地达到100g+)，而机器内存只有可怜的4g，不仅要承担db server角色，同时也要作为web server，可想而知这台机器的命运是及其惨烈的，只要ms sql server一启动，内存使用率立马飙升至99%。没办法，只能升内存，两根8g共16g的内存换上，结果还是一样，内存瞬间被秒杀（cpu利用率在0%徘徊）。由于是pc机，内存插槽共俩，目前市面上最大的单根内存为16g（价格1k+），就算买回来估计内存还是不够（卧槽，pc机伤不起啊），看样子别无它法 -- 删数据！！！
删除数据 - 说的容易， 不就是delete吗？靠，如果真这么干，我xxx估计能“知道上海凌晨4点的样子”（kb，sorry，谁让我是xxx的programmer，哥在这方面绝对比你牛x），而且估计会暴库（磁盘空间不足，产生的日志文件太大了）。
二、沙场点兵 - 众里寻他千百度
为了更好地阐述我所遇到的困难和问题，有必要做一些必要的测试和说明，同时这也是对如何解决问题的一种探究。因为毕竟这个问题的根本是如何来更好更快的操作数据，说到底就是delete、update、insert、truncate、drop等的优化操作组合，我们的目的就是找出最优最快最好的方法。为了便于测试，准备了一张测试表employee
--create table employeecreate table [dbo].[employee] ( [employeeno] int primary key, [employeename] [nvarchar](50) null, [createuser] [nvarchar](50) null, [createdatetime] [datetime] null);
1. 数据插入pk1.1. 循环插入，执行时间为38026毫秒
--循环插入set statistics time on;declare @index int = 1;declare @timer datetime = getdate();while @index 100000begin insert [dbo].[employee](employeeno, employeename, createuser, createdatetime) values(@index, 'employee_' + cast(@index as char(6)), 'system', getdate()); set @index = @index + 1;endselect datediff(ms, @timer, getdate()) as [执行时间(毫秒)];set statistics time off;
1.2. 事务循环插入，执行时间为6640毫秒
--事务循环begin tran;set statistics time on;declare @index int = 1;declare @timer datetime = getdate();while @index 100000begin insert [dbo].[employee](employeeno, employeename, createuser, createdatetime) values(@index, 'employee_' + cast(@index as char(6)), 'system', getdate()); set @index = @index + 1;endselect datediff(ms, @timer, getdate()) as [执行时间(毫秒)];set statistics time off;commit;
1.3. 批量插入，执行时间为220毫秒
set statistics time on;declare @timer datetime = getdate();insert [dbo].[employee](employeeno, employeename, createuser, createdatetime)select top(100000) employeeno = row_number() over (order by c1.[object_id]), 'employee_', 'system', getdate()from sys.columns as c1 cross join sys.columns as c2order by c1.[object_id]select datediff(ms, @timer, getdate()) as [执行时间(毫秒)];set statistics time off;
1.4. cte插入，执行时间也为220毫秒
set statistics time on;declare @timer datetime = getdate();;with cte(employeeno, employeename, createuser, createdatetime) as( select top(100000) employeeno = row_number() over (order by c1.[object_id]), 'employee_', 'system', getdate() from sys.columns as c1 cross join sys.columns as c2 order by c1.[object_id])insert [dbo].[employee] select employeeno, employeename, createuser, createdatetime from cte;select datediff(ms, @timer, getdate()) as [执行时间(毫秒)];set statistics time off;
小结：
按执行时间，效率依次为：cte和批量插入效率相当，速度最快，事务插入次之，单循环插入速度最慢；单循环插入速度最慢是由于insert每次都有日志，事务插入大大减少了写入日志次数，批量插入只有一次日志，cte的基础是clr，善用速度是最快的。
2. 数据删除pk2.1. 循环删除，执行时间为1240毫秒
set statistics time on;declare @timer datetime = getdate();delete from [dbo].[employee];select datediff(ms, @timer, getdate()) as [执行时间(毫秒)];set statistics time off;
2.2. 批量删除，执行时间为106毫秒
set statistics time on;declare @timer datetime = getdate();set rowcount 100000;while 1 = 1begin begin tran delete from [dbo].[employee]; commit if @@rowcount = 0 break;endset rowcount 0;select datediff(ms, @timer, getdate()) as [执行时间(毫秒)];set statistics time off;
2.3. truncate删除，执行时间为0毫秒
set statistics time on;declare @timer datetime = getdate();truncate table [dbo].[employee];select datediff(ms, @timer, getdate()) as [执行时间(毫秒)];set statistics time off;
小结：
truncate太快了，清除10w数据一点没压力，批量删除次之，最后的delte太慢了；truncate快是因为它属于ddl语句，只会产生极少的日志，普通的delete不仅会产生日志，而且会锁记录。
三、磨刀霍霍 - 犹抱琵琶半遮面
由上面的第二点我们知道，插入最快和删除最快的方式分别是批量插入和truncate，所以为了达到删除大数据的目的，我们也将采用这两种方式的组合，其中心思想是先把需要保留的数据存放之新表中，然后truncate原表中的数据，最后再批量把数据插回去，当然实现方式也可以随便变通。
1. 保留需要的数据之新表中->truncate原表数据->还原之前保留的数据之原表中脚本类似如下
select * into #keep from original where createdate > '2011-12-31'truncate table originalinsert original select * from #keep
第一条语句会把所有要保留的数据先存放至表#keep中（表#keep无需手工创建，由select into生效），#keep会copy原始表original的表结构。ps：如果你只想创建表结构，但不拷贝数据，则对应的脚本如下
select * into #keep from original where 1 = 2
第二条语句用于清除整个表中数据，产生的日志文件基本可以忽略；第三条语句用于还原保留数据。
几点说明：
你可以不用select into，自己通过写脚本（或拷贝现有表）来创建#keep，但是后者有一个弊端，即无法通过sql脚本来获得对应的表生成script（我的意思是和原有表完全一致的脚本，即基本列，属性，索引，约束等），而且当要操作的表比较多时，估计你肯定会抓狂；既然第一点欠妥，那考虑新建一个同样的数据库怎么样？既可以使用现有脚本，而且生成的数据库基本一致，但是我告诉你最好别这么做，因为第一要跨库，第二，你得准备足够的磁盘空间。
2. 新建表结构->批量插入需要保留的数据->drop原表->重命名新表为原表create table #keep as (xxx) xxx -- 使用上面提到的方法（使用既有表的创建脚本），但是不能够保证完全一致；
insert #keep select * from original where clause
drop tbale original
exec sp_rename '#keep','original'
这种方式比第一种方法略快点，因为省略了数据还原（即最后一步的数据恢复），但是稍微麻烦点，因为你需要创建一张和以前原有一模一样的表结构，包括基本列、属性、约束、索性等等。
三、数据收缩 - 秋风少落叶
数据删除后，发现数据库占用空间大小并没有发生变化，此时我们就用借助强悍的数据收缩功能了，脚本如下，运行时间不定，取决于你的数据库大小，多则几十分钟，少则瞬间秒杀
dbcc shrinkdatabase(db_name)