Do you clean up after yourself?

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.

Tags: ,

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.