Is it better to drop temporary tables at the end of a stored procedure?
I have heard somewhere that there can be stored proc recompilation issues which affect performance when you explicitly drop temp tables?
Will SQL Server "Clean Up" once the stored proc is out of scope or should I "do it myself".
Which method has known performance benefits?
If resources aren´t an issue on your sqlserver you don´t need to drop temps yourself. sqlserver will drop them when the sp is done.
Be careful though when working with temps, more than five updates to a temp table could force sqlserver to recomple. Any Set statements in the sp will also force sqlserver to recompile(SET NOCOUNT ON for example).