SQL Server – Delete Vs Truncate

Posted: October 4, 2011 in SQLServer
Tags: ,

http://sqlservergeeks.com/blogs/RakeshMishra/sql-server-bi/76/delete-vs-truncate

Truncate table does not work in case if there is any foriegn key relation on the table. We have to drop the FK constraint before truncating the data and then we have to recreate the constraint.

Many places I have seen the following suggestion and it will not work.

 ALTER TABLE <TABLE1> NOCHECK CONSTRAINT ALL
ALTER TABLE <TABLE1> DISABLE TRIGGER ALL
TRUNCATE TABLE <TABLE1>
ALTER TABLE <TABLE1> CHECK CONSTRAINT ALL
ALTER TABLE <TABLE1> ENABLE TRIGGER ALL

More abt disabling indexes/constraints …

http://technet.microsoft.com/en-us/library/ms177456.aspx

Advertisements

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