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;
