TRUNCATE TABLE in SQL

TRUNCATE TABLE

Truncate is a commonly used command in SQL Server Management Studio (SSMS) to quickly delete all data from a table, while keeping the table structure intact. This can be very useful for removing large amounts of data, especially when compared to using the DELETE command which can take a long time to execute on larger tables. In this blog post, we will go over the core syntax for using Truncate in SSMS, and provide some examples of how it can be used. 

Syntax: 

The basic syntax for Truncate is as follows: 

Where table_name is the name of the table you want to truncate. 

Examples: 

Here are some examples of how Truncate can be used in SSMS: 

Example 1: Truncating a table 

To truncate a table, simply execute the following command: 

This will remove all data from the my_table table, while leaving the structure intact. 

Example 2: Truncating multiple tables 

You can also use Truncate to delete data from multiple tables at once, like this: 

This will remove all data from my_table1, my_table2, and my_table3. 

Example 3: Truncating a table with a foreign key constraint 

If the table you want to Truncate has a foreign key constraint, you need to disable the constraint before truncating. For example: 

This will disable the foreign key constraint on child_table, truncate parent_table, and then enable the constraint again. 

Advantages: 

  • Truncate is a very fast way to remove large amounts of data from a table. 
  • It keeps the table structure intact, making it very easy to add new data back into the table. 
  • Truncate can be used to delete data from multiple tables at once, making it very efficient. 

Disadvantages: 

  • Truncate cannot be used with WHERE clauses, meaning you can’t selectively remove data from a table. 
  • Truncate cannot be rolled back, so you need to be careful when using it. 

Conclusion: 

  • Truncate is a very useful command in SSMS for quickly removing large amounts of data from a table. It is fast and efficient, and can be used to delete data from multiple tables at once. However, it cannot be used with WHERE clauses, and cannot be rolled back, so use it carefully. 

Refer blog no.68 for: 

Difference between Drop, Delete and Truncate.