SQL data Insert, delete big Data

--Test table CREATE TABLE [dbo]. [Employee] (    [employeeno]INTPRIMARYKEY ,     [employeename] [nvarchar] (NULL,    [CreateUser] [  nvarchar](NULL,    [createdatetime ][datetime]NULL);

--1,Loop InsertSET STATISTICSTime 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 [execution Time (milliseconds)];SET STATISTICSTimeOFF;

--2,Transaction LoopsBEGIN TRAN;SET STATISTICSTime 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 [execution Time (milliseconds)];SET STATISTICSTimeOFF;COMMIT;
--3, BULK Insert

SET STATISTICSTime on;DECLARE @Timer DATETIME = GETDATE();INSERT [dbo].[Employee](Employeeno, EmployeeName, CreateUser, Createdatetime)SELECT TOP(100000) Employeeno=Row_number () Over(ORDER  byC1.[object_id]),'Employee_','system',GETDATE() fromSYS. COLUMNS asC1 Cross JOINSYS. COLUMNS asC2ORDER  byC1.[object_id]SELECT DATEDIFF(MS,@Timer,GETDATE()) as [execution Time (milliseconds)];SET STATISTICSTimeOFF;
--4, CET insertion

SET STATISTICSTime on;DECLARE @Timer DATETIME = GETDATE();; withCTE (Employeeno, EmployeeName, CreateUser, Createdatetime) as(    SELECT TOP(100000) Employeeno=Row_number () Over(ORDER  byC1.[object_id]),'Employee_','system',GETDATE()     fromSYS. COLUMNS asC1 Cross JOINSYS. COLUMNS asC2ORDER  byC1.[object_id])INSERT [dbo].[Employee] SELECTEmployeeno, EmployeeName, CreateUser, Createdatetime fromCTE;SELECT DATEDIFF(MS,@Timer,GETDATE()) as [execution Time (milliseconds)];SET STATISTICSTimeOFF;
--5, loop Delete

SET STATISTICS  on ; DECLARE @Timer DATETIME = GETDATE (); DELETE  from [dbo]. [Employee] ; SELECT DATEDIFF @Timer GETDATE  as [ Execution Time (milliseconds)] ; SET STATISTICS OFF;
--6, Bulk Delete

SET STATISTICSTime 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 [execution Time (milliseconds)];SET STATISTICSTimeOFF;
--6, truncate delete

  SET   STATISTICS  time  on  ;   DECLARE   @Timer   DATETIME   =   GETDATE   ();   TRUNCATE   TABLE   [  dbo  ] .  [  Employee  ]  ;   SELECT   DATEDIFF  (MS,  @Timer ,  GETDATE  ())  as   [  Execution Time (milliseconds)  ]  ;   SET   STATISTICS  Time  OFF ;