What are the differences between DROP a table and TRUNCATE a table?

Posted on

DROP TABLE  – This will delete the table and its data.

TRUNCATE TABLE  – This will delete the data of the table, but not the table definition.

Deleting records from a table logs every deletion and executes delete triggers for the records deleted. Truncate is a more powerful command that empties a table without logging each row. SQL Server prevents you from truncating a table with foreign keys referencing it, because of the need to check the foreign keys on each row.

Truncate is normally ultra-fast, ideal for cleaning out data from a temporary table. It does preserve the structure of the table for future use.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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