Empty a SQL Server table (TRUNCATE)

How do I clear all the data in a SQL Server table with a query? From SSMS, connect to the SQL Server database and use the TRUNCATE TABLE command to clear the data from the table.

Note that the TRUNCATE command is different from the DELETE query in that all data is removed without any filter. The TRUNCATE command is therefore faster and recommended.

Empty a SQL Server table with the Truncate Table query

This command deletes all the data stored in the table. Be careful, this command is irreversible. Remember to check your database backups to avoid losing data.

Note that this action is irreversible.

Simply use a T-SQL query from SQL Server Management Studio, with the TRUNCATE TABLE command.

Note that the TRUNCATE query is different from the DELETE command, especially in terms of performance. A DELETE FROM command scans all rows before deleting and allows you to filter the rows to be deleted.


Minimum rights needed on the table to execute a TRUNCATE

The TRUNCATE command has more impact than a simple DELETE, especially because the truncate cannot be undone via the transaction log. The minimum right to execute a TRUNCATE command is ALTER. Because the DBMS considers this to be a modification of the table.

To delete the table completely, the data structure and the data must be deleted. To avoid generating error messages, then test the existence of the SQL Server table before deleting it.

Be the first to comment

Leave a Reply

Your email address will not be published.