I noticed that some TSQL operations that I perform regularly have a virtual memory cost.
For instance, if I create a new non-indexed column B, copy data from non-indexed column A to column B, then drop column A, then the database .mdf size goes up even though there is no new data in it.
Another is if I run ALTER INDEX ALL ON Table1 REBUILD for all tables in a database, again I see the overall size of the .mdf go up. I just ran such an operation on one database and its size went up from 193 MB to 258 MB, which is about 33%!!!
There may be very valid reasons why this happens but is there a way to recapture the memory?