SQL Server系列之删除大量数据
一、写在前面 - 想说爱你不容易
为了升级
数据库至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 Employee
CREATE TABLE [dbo].[Employee] (
[EmployeeNo] INT PRIMARY KEY,
[EmployeeName] [nvarchar](50) NULL,
[CreateUser] [nvarchar](50) NULL,
[CreateDatetime] [datetime] NULL
);
1. 数据插入PK
1.1. 循环插入，执行时间为38026毫秒
--循环插入
SET STATISTICS TIME ON;
DECLARE @Index INT = 1;
DECLARE @Timer DATETIME = GETDATE();
WHILE @Index <= 100000
BEGIN
INSERT [dbo].[Employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime) VALUES(@Index, 'Employee_' + CAST(@Index AS CHAR(6)), 'system', GETDATE());
SET @Index = @Index + 1;
END
SELECT 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 <= 100000
BEGIN
INSERT [dbo].[Employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime) VALUES(@Index, 'Employee_' + CAST(@Index AS CHAR(6)), 'system', GETDATE());
SET @Index = @Index + 1;
END
SELECT 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 C2
ORDER 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. 数据删除PK
2.1. 循环删除，执行时间为1240毫秒
SET STATISTICS TIME ON;
DECLARE @Timer DATETIME = GETDATE();
DELETE FROM [dbo].[Employee];
SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
SET STATISTICS TIME OFF;