If I create a table variable like this:
Declare @MyTable Table(ID int,Name varchar(50))
Is it better on the server to run a delete query on the variable at the end of your queries? Sort of like closing an object?
Delete From @MyTable
Or is it unnecessary?
Using Delete will be worse.
Instead of just having SQL Server implicitly drop the table variable when it goes out of scope (which has minimal logging) you will also add fully logged delete operations for each row to the tempdb transaction log.
I can't see how this will be better for performance - it will at best be the same (since the @table will be dropped when it's out of scope anyway), and at worst will be more expensive because it actually has to perform the delete first. Do you think there is any advantage in doing this:
DELETE #temptable;
DROP TABLE #temptable;
Instead of just this:
DROP TABLE #temptable;
I will admit that I haven't tested this in the @table case, but that's something you can test and benchmark as well. It should be clear that in the above case running the DELETE first will take more resources than not bothering.
There is probably a reason there is no way to DROP TABLE @MyTable; or DEALLOCATE @MyTable; - but nobody here wrote the code around table variables and it is unlikely we'll know the official reason(s) why we can't release these objects early. But dropping the table wouldn't mean you're freeing up the space anyway - you're just marking the pages in a certain way.
