jump to navigation

When to use delete, truncate and drop SQL statements May 17, 2008

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
trackback

 

Some people who are beginning to find their way around SQL statements experience some difficulty differentiating between the Delete,Truncate and Drop SQL commands.

DELETE

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.

TRUNCATE

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. 

DROP

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.

 

Comments»

1. sriram - July 18, 2008

Good description about the 3 statements.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: