Which one is better Drop or Delete ?

Question

I have 3 tables one having 100 Records and other having 250 Records, both these tables are full refresh tables on a weekly basis. Both the tables are date partitioned, having USI on 2 columns each.
These 2 tables store only 2 years of data from the load date.
Load strategy Currently what we have is Delete the entire tables using Delete all and load the data.
Do you think we will have some savings if we approach with below one?
Instead of deleting, if we drop the table and re-create with fresh DDL with only 5 years of partition weekly and load data.

Answer 1

If there are only 250 rows at most, it seems a bit of overkill to partition...

If there are no referencing foreign keys, you can use TRUNCATE TABLE.

Else I would prefer to delete the data. I don't like changing the schema of the database outside installation of new versions.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Hi,

Truncate and delete are good option instead of drop .

I dont see any reason to drop the table schema and then re create it again .

Thanks

Abhishek