Lowell (3/26/2015)

---

my fast proof of concept: drop and recreate is definitely slower

in a measly 1000 iterations,these were my stats:

2113 milliseconds for Drop and Recreate

543 milliseconds for Truncate Existing Table

and my test harness:

SET STATISTICS IO OFF

SET NOCOUNT ON

DECLARE @i int = 0

DECLARE @Start datetime = getdate();

SET @Start = getdate();

WHILE @i <1000

BEGIN

IF OBJECT_ID('tempdb.[dbo].[#tmp]') IS NOT NULL

DROP TABLE [dbo].[#tmp]

--some percentage (1/5 ) of iterations will be skipped as no rows to simulate occasional data

SELECT TOP (@i % 5) name INTO #tmp FROM sys.columns

SET @i=@i + 1

END

PRINT CONVERT(varchar,datediff(ms,@Start,getdate())) +' milliseconds for Drop and Recreate'

IF OBJECT_ID('tempdb.[dbo].[#tmp2]') IS NOT NULL

DROP TABLE [dbo].[#tmp2]

CREATE TABLE [dbo].[#tmp2] (

[name] SYSNAME NULL)

SET @Start = getdate();

SET @i = 0;

WHILE @i <1000

BEGIN

TRUNCATE TABLE [#tmp2]

--some percentage (1/5 ) of iterations will be skipped as no rows to simulate occasional data

INSERT INTO [#tmp2]

SELECT TOP (@i % 5) name FROM sys.columns

SET @i=@i + 1

END

PRINT CONVERT(varchar,datediff(ms,@Start,getdate())) +' milliseconds for Truncate Existing Table'