SQL Truncate

Galaxy Glossary

How do you quickly remove all rows from a table in SQL?

TRUNCATE TABLE is a SQL command used to remove all rows from a table. It's faster than DELETE but doesn't allow for filtering or using a WHERE clause.

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

The TRUNCATE TABLE command is a powerful tool for quickly emptying a table in a SQL database. Unlike the DELETE command, which can be used to remove rows based on specific criteria, TRUNCATE TABLE removes *all* rows from the table. This makes it significantly faster than DELETE, especially for large tables, because it doesn't require processing individual row deletions. It's crucial to understand that TRUNCATE TABLE is a DML (Data Manipulation Language) statement, meaning it directly modifies the data within a table. It's important to note that TRUNCATE TABLE is a DML statement that permanently removes all rows from a table. This operation cannot be undone unless you have a backup or use a transaction that can be rolled back. It's also important to remember that TRUNCATE TABLE does not return any rows or information about the rows that were deleted. This is a key difference from DELETE, which often returns a count of the rows deleted.

Why SQL Truncate is important

TRUNCATE TABLE is essential for quickly clearing a table's contents, especially when preparing for large-scale data loads or maintenance tasks. Its speed advantage over DELETE makes it a valuable tool for database administrators and developers.

Example Usage


-- Extracting the first 10 characters of a string
SELECT SUBSTRING('Hello, World!', 1, 10);

-- Extracting characters from the 6th position to the end
SELECT SUBSTRING('Hello, World!', 6);

-- Extracting a specific portion of a string
SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 5, 5);

-- Handling potential errors (important):
SELECT SUBSTRING('Hello', 10, 5);
-- This will return an empty string, not an error.  Be mindful of string length.

-- Using SUBSTRING with a table
SELECT
    customer_name,
    SUBSTRING(email, 1, INSTR(email, '@') - 1) AS email_username
FROM
    customers;

Common Mistakes

Forgetting that TRUNCATE TABLE removes *all* rows without allowing for filtering.
Trying to use TRUNCATE TABLE with a WHERE clause (it's not supported).
Not realizing that TRUNCATE TABLE is a permanent operation and should be used with caution.
Assuming TRUNCATE TABLE returns a row count; it does not.

SQL Truncate

How do you quickly remove all rows from a table in SQL?

Description

Why SQL Truncate is important

Example Usage

Common Mistakes

Want to learn about other SQL terms?

With Cte In SQL

Where Vs Having SQL

Windows Functions In SQL