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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment