Embed URL

HTTPS clone URL

SSH clone URL

You can clone with HTTPS or SSH.

Download Gist

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

View TRUNCATE and DROP are both minimally logged.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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.