Demonstration that TRUNCATE and DROP are logged and just as fast as one another.

  • Download Gist
TRUNCATE and DROP are both minimally logged.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
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;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.