I want to drop a table which is 100 GB. Shall i execute drop command to remove the particular table from database Drop a table

Question

I want to drop a table which is 100 GB. Shall i execute "drop command" or truncate then drop command to remove the particular table from database in SQL server 2008R2.

Answer 1

Hi,

Drop & Truncate are 2 different operations all together. While Truncate removes the data from the table and resets the identity column value (in case available), drop table will remove all the information of the table. Hence depending on your need you should be using "drop table" command rather then "Truncate table".

You can refer below link for the reference

http://www.c-sharpcorner.com/blogs/difference-between-truncate-delete-and-drop-in-sql-server1 

https://dba.stackexchange.com/questions/4163/why-use-both-truncate-and-drop 

---

Sandeep Prajapati

Answer 2

DROP TABLE itself would be sufficient for you as the emphasis is to remove the table

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 3

If you have FKs referencing to other tables you won't truncate /drop

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

DROP is just as fast as TRUNCATE.
Like TRUNCATE, DROP is a minimally logged operation

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence