SQL Server系列之删除大量数据(二)
2.2. 批量删除，执行时间为106毫秒
SET STATISTICS TIME ON;
DECLARE @Timer DATETIME = GETDATE();
SET ROWCOUNT 100000;
WHILE 1 = 1
BEGIN
BEGIN TRAN
DELETE FROM [dbo].[Employee];
COMMIT
IF @@ROWCOUNT = 0
BREAK;
END
SET 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 Original
INSERT 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)