When to use delete, truncate and drop SQL statements May 17, 2008Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Some people who are beginning to find their way around SQL statements experience some difficulty differentiating between the Delete,Truncate and Drop SQL commands.
We can use the DELETE command to remove rows from a table. We must specify a WHERE clause in our DELETE statements so only some rows will be removed.If we do not do that all rows will be removed and that something we most certainly never want. DELETE operations will cause all DELETE triggers on the table to fire.
We can use the TRUNCATE statement to remove all rows from a table. A very important thing to note is that the TRUNCATE operation cannot be rolled back and no triggers are fired.
The DROP statement removes a table from the database. All the tables’ rows, indexes and privileges will also be removed. The operation cannot be rolled back and no triggers will be fired.