woensdag 20 april 2011

How to drop a temporary table

What is the best way to DROP a temporay table?

The most common way I always use to drop tables:
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[TableName]') AND type = N'U')
DROP TABLE TableName

Yes, this is the standard SQL Server way and I always think that what they build is also the best practice.

When you ofcourse do this for a temporay table that you did create on DatabaseX using the above code you might find out that the table is not dropped:

USE DatabaseX
GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[#tmpTableName]') AND type = N'U')
DROP TABLE #tmpTableName
GO

Why is this table not dropped, I'm sure it exists and I'm in my session you say?

The simple thing is that temporay tables are not created in you current database but always in the system database tempdb. That needs us to change the query to:

IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[tmpdb]..[#TableName]') AND [type] = N'U')
DROP TABLE [#tmpTableName]
GO


Is this correct already? No it's not, yes by accident it might work but why? It might be (it happend to me) that you have a current object ID in the DatabaseX.sys.objects table that has the same ID as the #tmpTableName in the tempdb. This might be certain when you leave out the [type] where clause.

To be realy sure the statement should be:
IF EXISTS (SELECT 1 FROM tempdb.sys.objects WHERE [object_id] = OBJECT_ID(N'[tmpdb]..[#TableName]') AND [type] = N'U')
DROP TABLE [#tmpTableName]
GO

Did you see the differences?

Geen opmerkingen: