SQL 数据插入、删除大数据

--测试表
CREATE TABLE [dbo].[Employee] (
    [EmployeeNo] INT PRIMARY KEY,
    [EmployeeName] [nvarchar](50) NULL,
    [CreateUser] [nvarchar](50) NULL,
    [CreateDatetime] [datetime] NULL
);
--1、循环插入
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;
--2、事务循环
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;
--3、批量插入
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;
--4、CET插入
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;
--5、循环删除
SET STATISTICS TIME ON;
DECLARE @Timer DATETIME = GETDATE();
DELETE FROM [dbo].[Employee];
SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
SET STATISTICS TIME OFF;
--6、批量删除
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;
--6、Truncate删除
SET STATISTICS TIME ON;
DECLARE @Timer DATETIME = GETDATE();
TRUNCATE TABLE [dbo].[Employee];
SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
SET STATISTICS TIME OFF;