2

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.

7

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 
2

They are referencing the column object_type in the sys.objects table. The identifies as:

U = Table (user-defined)

Reference

There is no real performance gain or loss here, their method is just more precise to identify that it must be X named object and it must be Y object type.

0

What the lookups are essentially doing is

SELECT object_id FROM tempdb.sys.tables WHERE name = '#TempTbl'

and

SELECT object_id FROM tempdb.sys.tables WHERE name = '#TempTbl' AND type = 'u'

(not quite this: if you look in tempdb.sys.tables the names are more like #TempTbs___________________000001234 where the code at the end differs per session, hence two or more active sessions can have temporary tables of the same name)

If you run similar queries yourself (in tempdb or any other) you'll see that the query plan is exactly the same so there is unlikely to be significant performance difference.

Including the type in the search makes it explicit that you are expecting to find a user defined table, so is safer as it won't return the ID of another object type if such exists with that name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.