Sql-server – performance difference in the 2 temp table initializations

sql server

I've recently come across a interesting practice where I work. I've noticed that some developers use the following way to initialize a sql server temp table: 

if object_id('tempdb..#TempTbl','u') is not null
drop table #TempTbl

Other teammates will use: 

if object_id('tempdb..#TempTbl') is not null
drop table #TempTbl

My questions:

  1. Is there a performance difference between the 2 initializations?
  2. What purpose does the ,'u' piece of code serve in the first statement?

I've tried researching msdn to get more information and it doesn't seem like either statement has a real difference. They produce the same results, but I'm curious about performance or other related factors.

Best Answer

Is there a performance difference between the 2 initializations?

No. There is no performance difference. If you use set statistics time, io ON and check there is no CPU or IO involved

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

What purpose does the ,'u' piece of code serve in the first statement?

The syntax for object_id is

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]
object_name' [ ,'object_type' ] )

The u specifies its a user object. It is called user defined table in sys.objects

I think it is good coding practice to define what you are doing. So in the 1st example the developer is saying that if #TempTbl table exists then drop it. I have seen poor naming where people name views like tables, do not use schema prefix and other bad habits !

In SQL server 2016, you will see a new syntax ..

DROP TABLE IF EXISTS
Related Question