My requirement is to drop the unused tables which will be in size of 10 Gb to 1 TB (it will vary based on load).
what is the best apprach of dropping those tables.
Which approch is Good?
This depends on your requirement . Delete is a DML command whereas Drop is a DDL command. Logging and triggers are done while perofrming a delete and it is a rollback type transaction. Drop being a DDL has no such repercussions and so cannot be a rolled back.
Looking at your requirements :
Option 1 : Drop is definately faster
Option 2 : Delete is slower but a safer option.
I concur with solveraditya's reasoning.
Personally, if I expect to fill that table again anytime soon or need it as a place holder for an ETL, I just use DELETE. In an automated process, I would defintely use DELETE as a step and then DROP as another (time lapse or user approval) to intervene if necessary giving way to the rollback features mentioned.
If the absolute end you want is to DROP the table, I wouldn't bother with DELETE and let it go while hot.
Unless the DELETE is wrapped in a transaction (or there are Triggers or FKs defined on the table) it will be a FastPath Delete without Transient Journal, so any user approval will be too late. And of course you should be sure if you actually want to delete before you do it.
So a single DROP TABLE is always preferred over DELETE/DROP.
Are you going to use again an identical table in the same database in the future?
Is this going to happen for many tables?
If it is the case I'd go for the delete and keep the table in place rather than drop and create again in the future.