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

TRUNCATE and DROP are both minimally logged.sql

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;