I have created a table : Table1. Inserted 100 thousand rows and deleted. I did the same around 100 times ( inserting and deleting). Will it cause of increasing the database and table size every time? ( I am using Delete instead of Truncate).
2 Answers
WHen you use delete the rows actually not deleted but marked as GHOST records, which will finally deleted by dedicated process named GHOST CLEANUP which works relatively slow in background
To force deletion of ghost records run:
DBCC FORCEGHOSTCLEANUP 
No, actually deletion doesn't free data space instantly but marks corresponding data pages as free for that table. So when you insert data for the same table, those particular pages get filled up first. In any case, after a while the background process de-allocates the free pages and finally reduces the page size.
Here is a good article on this I think.
