DELETE Query and TRUNCATE Function in SQL

Delete and Truncate commands in SQL

The Delete query in SQL only deletes records from the table, and it doesn’t make any changes in the definition, i.e., it only manipulates. Hence, it is DML (Data Manipulation Language). The Truncate command in SQL removes all rows from a table, i.e., it reinitializes the identity by making changes in the data definition. That’s the reason it is a DDL (Data Definition Language).

Watch this Delete Query in SQL vs Truncate Command in SQL video

A DELETE query in SQL is used to remove one or more rows from a table based on certain conditions. It’s a powerful operation that requires caution, as the deleted data is permanently removed from the table. Here’s the basic structure of a DELETE query:

DELETE FROM table_name
WHERE condition;

Let’s break down the components:

• DELETE FROM: This part of the query indicates that you want to delete rows from a specific table.
• table_name: Replace this with the actual name of the table from which you want to delete data.
• WHERE condition: This is an optional part of the query that specifies the condition that must be met for a row to be deleted. If you omit this part, all rows in the table will be deleted.

For example, consider a table named “Employees” with columns “EmployeeID,” “FirstName,” “LastName,” and “Salary.” If you want to delete an employee with a specific ID, the query might look like this:

DELETE FROM Employees
WHERE EmployeeID = 101;

It’s important to be cautious while using DELETE queries, especially without a specific condition, as they can lead to unintended data loss. To avoid accidents, it’s recommended to perform the following steps:

1. Backup: Before running DELETE queries, make sure you have a backup of your data. This ensures that you can recover the data if something goes wrong.
2. Use SELECT First: If you’re unsure about the records that will be deleted, you can first run a SELECT query with the same condition to preview the data that will be affected.
3. Transaction: Wrap the DELETE query within a transaction if possible. This way, you can roll back the transaction if something unexpected occurs.
4. Restrict Permissions: Limit DELETE permissions to authorized users only. This prevents accidental or malicious deletion of data.
5. Test in Development: Always test DELETE queries in a development or testing environment before applying them to production data.

Examples of DELETE Query in SQL

here are a few examples of DELETE queries in SQL with different scenarios:

Example 1: Basic DELETE Query Suppose you have a table named “Products” with columns “ProductID,” “ProductName,” and “StockQuantity.” You want to delete a product with a low stock quantity (less than 10):

DELETE FROM Products
WHERE StockQuantity < 10;

This query will delete all rows where the stock quantity is less than 10.

Example 2: DELETE with Specific Condition Consider a table named “Orders” with columns “OrderID,” “CustomerID,” and “OrderDate.” You want to delete an order with a specific ID:

DELETE FROM Orders
WHERE OrderID = 12345;

This query will delete the order with an OrderID of 12345.

Example 3: DELETE All Records Suppose you want to start fresh by deleting all records from a table named “LogEntries”:

DELETE FROM LogEntries;

This query will delete all records from the “LogEntries” table.

Example 4: DELETE with JOIN If you have a related table structure, you might use a DELETE query with a JOIN. Suppose you have a “Customers” table and an “Orders” table, and you want to delete a specific customer and all their associated orders:

DELETE Customers, Orders
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerID = 789;

This query will delete the customer with CustomerID 789 and all orders associated with that customer.

Example 5: DELETE with Subquery Suppose you have a table named “Employees” and you want to delete employees who have not placed any orders in the “Orders” table:

DELETE FROM Employees
WHERE EmployeeID NOT IN (SELECT DISTINCT EmployeeID FROM Orders);

This query will delete employees who do not have a corresponding entry in the “Orders” table.

The TRUNCATE command in SQL is used to quickly and efficiently remove all rows from a table, effectively resetting it to an empty state. Unlike the DELETE statement, which removes rows one by one and generates individual transaction logs for each deletion, TRUNCATE works by deallocating data pages and is typically faster, especially for large tables.

Here’s the syntax of the TRUNCATE command:

TRUNCATE TABLE table_name;

• TRUNCATE TABLE: This part of the command indicates that you want to remove all rows from the specified table.
• table_name: Replace this with the name of the table you want to truncate.

Keep in mind the following points when using the TRUNCATE command:

1. Data Deletion: TRUNCATE deletes all rows from the table, and the operation is not reversible. It’s a quicker alternative to DELETE for removing all data.
2. Auto-commit: TRUNCATE is typically an auto-commit operation, meaning it’s immediately committed to the database without needing an explicit transaction.
3. Table Structure: TRUNCATE retains the table structure, constraints, and indexes. It’s like starting with an empty table that has the same schema.
4. Permissions: TRUNCATE requires appropriate permissions on the table. Users must have at least the ALTER privilege on the table.
5. Referential Integrity: If the table is referenced by foreign keys in other tables, you might need to temporarily disable or drop these constraints before using TRUNCATE.
6. Cannot Be Rolled Back: Unlike transactions, TRUNCATE cannot be rolled back. Once the command is executed, the data is permanently removed.

Examples of SQL Truncate

Example 1: Basic TRUNCATE Suppose you have a table named “Orders” with columns “OrderID,” “CustomerID,” and “OrderDate.” You want to quickly remove all rows from the table:

TRUNCATE TABLE Orders;

This command will remove all rows from the “Orders” table while keeping its structure intact.

Example 2: TRUNCATE with Constraints If the table you’re trying to truncate is referenced by foreign keys in other tables, you might need to temporarily drop or disable the constraints:

-- Drop foreign key constraints
ALTER TABLE OtherTable
DROP CONSTRAINT FK_ConstraintName;

-- Truncate the main table
TRUNCATE TABLE MainTable;

-- Recreate the foreign key constraints
ALTER TABLE OtherTable
ADD CONSTRAINT FK_ConstraintName FOREIGN KEY (ForeignKeyColumn) REFERENCES MainTable (PrimaryKeyColumn);

In this example, you need to drop the foreign key constraints before truncating the main table and then recreate them afterward.

Example 3: TRUNCATE with Identity Columns If your table has an identity column (auto-incrementing), the identity value will be reset after truncating the table:

TRUNCATE TABLE MyTable;
DBCC CHECKIDENT ('MyTable', RESEED, 0);

After truncating, the identity column’s value will start from 1 again.

Example 4: TRUNCATE with Log Space Reclamation TRUNCATE can help in freeing up the log space used by the transactions. This can be beneficial when your transaction log becomes large due to many DELETE operations:

BACKUP LOG YourDatabaseName TO DISK = 'C:BackupYourDatabaseLogBackup.bak';
TRUNCATE TABLE YourTable;

In this example, you back up the transaction log and then truncate the table to release the log space.

Example 5: TRUNCATE with WHERE Condition While TRUNCATE doesn’t support a WHERE clause like DELETE does, you can use a temporary table to achieve a similar result:

SELECT *
INTO #TempTable
FROM YourTable
WHERE SomeCondition;

TRUNCATE TABLE YourTable;

INSERT INTO YourTable
SELECT *
FROM #TempTable;

DROP TABLE #TempTable;

n this scenario, you first select the rows you want to keep into a temporary table, then truncate the main table, and finally re-insert the selected rows back into the main table.

Remember, the TRUNCATE command is a powerful tool for quickly removing all rows from a table. However, due to its irreversible nature, ensure that you have a backup of your data and understand the potential impacts before using it, especially in production environments.

SQL Delete vs SQL Truncate

Well, this how we work with Delete query in SQL and where to use Truncate command in SQL. The table below gives a quick difference between these two commands.

This bring us to end of this part of tutorial where we learned how delete and truncate command in SQL. Here we learned how to delete column in SQL, how to delete row in SQL and how to delete table in SQL, and we learned how to use truncate table in SQL.