Level 6 · Empty it, clean it or drop it?

This quiz checks the difference between the three ways of removing data, and the data-type choices you make when you create a table in the first place.

Three levels of removal

statement	what goes	what stays
DELETE FROM t WHERE ...	rows matching the condition	the table and every other row
DELETE FROM t (no WHERE)	every row, one by one	the empty table
TRUNCATE TABLE t	every row, in one operation	the empty table, columns and types
DROP TABLE t	the table itself	nothing

DELETE is the precise tool: it takes a WHERE, and it can be rolled back inside a transaction in every major database. TRUNCATE is the bulk tool for “empty this table now”, much faster on large tables because it does not process rows individually. DROP TABLE is for tables you no longer need.

A daily import table is the textbook TRUNCATE case. The job runs: truncate, insert today’s file, count the rows, compare with the file’s row count. If the load fails halfway, the table is either empty or partly loaded, which the row count check catches.

Pick types that fit the data

The table you empty every morning was defined once with CREATE TABLE, and its types decide what it accepts:

• Money in DECIMAL, not FLOAT. DECIMAL(10, 2) stores exact cents. DOUBLE and FLOAT store binary approximations: in DuckDB, 0.1::DOUBLE + 0.2::DOUBLE is 0.30000000000000004, so equality checks and sums of many prices drift. The same 0.1 + 0.2 with decimals is exactly 0.3.
• Dates in DATE or TIMESTAMP, not text. A text column sorts ‘11/3/2025’ before ‘2/1/2025’ and cannot do date arithmetic.
• Flags in BOOLEAN. is_active in the store’s products table is a BOOLEAN: true or false, with no room for ‘Yes’, ‘Y’ and ‘y’.
• Ids as INTEGER with a primary key, so duplicates are refused at insert time.

Common mistakes

• Dropping and recreating a table every day instead of truncating it, so its definition lives in two places and drifts.
• Assuming TRUNCATE can be undone everywhere. In Postgres, DuckDB and SQL Server it rolls back inside a transaction; in MySQL and Oracle it commits immediately.
• Storing prices as FLOAT because “it is a number”.

In SQL Server

T-SQL requires the word TABLE: TRUNCATE TABLE dbo.SupplierImport;. Its money-friendly type is DECIMAL(10,2) (there is also MONEY, which many teams avoid because of rounding in division), flags are BIT (0 or 1, no true BOOLEAN type), text is usually NVARCHAR(n), and date-times are DATETIME2. TRUNCATE fails on a table referenced by a foreign key; DELETE does not.