Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Demonstration that TRUNCATE and DROP are logged and just as fast as one another.
SET NOCOUNT ON;
USE [tempdb];
CREATE TABLE a_farting_farthing (
an_integer INT DEFAULT (1)
);
INSERT INTO a_farting_farthing
DEFAULT VALUES;
GO
-- Use the power of exponential growth.
-- This is the fastest way to create a massive table *from nothing*.
-- Number of rows created will be 2^x, where x is the number after the GO.
INSERT INTO a_farting_farthing
SELECT an_integer
FROM a_farting_farthing;
GO 27 -- This will produce >134 million rows.
DECLARE @row_count INT;
SELECT @row_count = COUNT(*)
FROM a_farting_farthing;
PRINT CHAR(13) + 'initial row count: ' + CAST(@row_count AS VARCHAR(10));
-- begin operation TROP (i.e. TRUNCATE/DROP)
BEGIN TRANSACTION;
PRINT CHAR(13) + 'TRUNCATE';
SET STATISTICS TIME ON;
TRUNCATE TABLE a_farting_farthing;
SET STATISTICS TIME OFF;
ROLLBACK TRANSACTION;
SELECT @row_count = COUNT(*)
FROM a_farting_farthing;
PRINT CHAR(13) + 'TRUNCATE row count: ' + CAST(@row_count AS VARCHAR(10));
BEGIN TRANSACTION;
PRINT CHAR(13) + 'TRUNCATE then DROP';
SET STATISTICS TIME ON;
TRUNCATE TABLE a_farting_farthing;
DROP TABLE a_farting_farthing;
SET STATISTICS TIME OFF;
ROLLBACK TRANSACTION;
SELECT @row_count = COUNT(*)
FROM a_farting_farthing;
PRINT CHAR(13) + 'TRUNCATE then DROP row count: ' + CAST(@row_count AS VARCHAR(10));
BEGIN TRANSACTION;
PRINT CHAR(13) + 'DROP';
SET STATISTICS TIME ON;
DROP TABLE a_farting_farthing;
SET STATISTICS TIME OFF;
ROLLBACK TRANSACTION;
SELECT @row_count = COUNT(*)
FROM a_farting_farthing;
PRINT CHAR(13) + 'DROP row count: ' + CAST(@row_count AS VARCHAR(10));
GO 10
--DROP TABLE a_farting_farthing;
@JeffModen

This comment has been minimized.

Copy link

@JeffModen JeffModen commented Jul 29, 2021

Hi Nick,

I appreciate anyone that steps up to the plate with coded examples so thanks for that.

Just a bit of a hint... your method for populating the table took, 00:02:59 (hh:mi:ss) on my machine. Try the following on your machine. On my machine, it took just of 18 seconds for 135 Million rows even, It also has a different number for each row instead of them all being the same, which is wicked important when using such tables to test for performance because they don't have a cardinality of just 1.

--===== Presets    
    USE tempdb;
    SET NOCOUNT OFF --We're only working with a single rowcount this time.
;
--===== Create the test table
   DROP TABLE IF EXISTS dbo.a_farting_farthing;
 CREATE TABLE dbo.a_farting_farthing
        (
        an_integer INT DEFAULT (1)
        )
;
--===== Poulate the table courtesy of Itzik Ben-Gans "cascading CTE" (my name for it) method
     -- with a little assistance by using "Minimal Logging" and a "Moden Mod".
 WITH
    H1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))H0(N))
,Tally(N) AS (SELECT TOP 135000000 ROW_NUMBER() OVER (ORDER BY @@SPID)
                FROM H1 a,H1 b,H1 c,H1 d,H1 e,H1 f,H1 g,H1 h)
 INSERT INTO dbo.a_farting_farthing WITH (TABLOCK)
        (an_integer)
 SELECT an_integer = t.N
   FROM Tally t
 OPTION (RECOMPILE)
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment