Lots of people think that all the temp tables created inside the stored procedures should be dropped and many often say, that dropping will take extra CPU cycles and it should be ignored. I am sure just like many people out there, you may have wondered this question as well and it is quite possible that you have also tried out various experiments with it.
During the course of my consultancy, I have been facing this question pretty much at the regular interval. I have explained my findings in the SQL in the Sixty Seconds video here.
In this video, I do a simple demonstration of running two stored procedures. In one stored procedure, I create a temp table and drop it and in the second one, I only created a temp table but do not drop it. I run both the stored procedure for 5000 times and compare their performance.
Honestly, I have run many different stored procedures and many different times. Every single time, I have realized that both the scenario are comparable to each other and give very similar performance.
Here is the code which I have used in the video.
CREATE OR ALTER PROCEDURE TempDrop AS CREATE TABLE #TempTable (ID BIGINT) INSERT INTO #TempTable (ID) VALUES (1) DROP TABLE #TempTable; GO CREATE OR ALTER PROCEDURE TempNoDrop AS CREATE TABLE #TempTable (ID BIGINT) INSERT INTO #TempTable (ID) VALUES (1) -- DROP TABLE #Temp1; GO
Now you may wonder what is my actual preference in this scenario. To know my preference, you will have to watch the video.
If you are wondering why it is not required to drop the temp table at the end of the stored procedure, well, it is because when the stored procedure completes execution, it automatically drops the temp table when the connection/session is dropped which was executing it.
Well, that’s it. You may subscribe to my YouTube channel here.
Reference: Pinal Dave (https://blog.sqlauthority.com)