G’day folks,

I remember a fair few years ago when I was relativly new to TSQL creating a stored procedure that used several temporary tables and had faily complex logic.

Occasionally during development the stored procedure would fail with errors – aside from the fact that the stored procedure had issues it left me with another problem – becuase the temp tables were hanging around in my session they caused more errors the next time I attempted to run the stored procedure as the tables already existed.

Now, obviously, I could just use another session and leave SQL SERVER to do the clean up because as we know local temporary tables only have scope to the session. As an aside you might think this means other users can’t see the data held in their copy of the local temporary table – but Paul White has some interesting takes on this.

Anyway, since that time I’ve also used code to check if a copy of the local temporary table exists and drop it if it does – I do this both at the start and end of my procedures.

IF OBJECT_ID('tempdb.dbo.#temptable') IS NOT NULL
BEGIN
PRINT 'About to drop table #temptable';
DROP TABLE #temptable
END

One of the things that I remember catching me out was the fact that temp tables only exist in tempdb and so that’s where we have to look for them – looking in the local database will simple result in the code returning false.

Now that I’ve learned a little more about SQL SERVER I’m curious if this has any (major) performanace impact \ benefit - so test carefully. I’ve personally never encountered any issues and if you do I’d love to hear about them.

Have a good day

Cheers

Martin.