Create a table called #test, and create a table called test in the tempdb, what's the difference?

Question

Hi guys,

In SQL Server 2005(2008 not tested), you can't create a temp function like #function_name, but you can create a functoin called function_name directly in tempdb. Does the function created in this way a temp function? What's the difference between a table called #table_name and the same named table directly created in tempdb?

Answer 1

One obvious difference is that it won't be dropped automagically when your connection ends but beyond that I find it quite useful for testing something out quickly on my development machine that I don't want to bother cleaning up explicitly. tempdb obviously gets recreated after server restart so shouldn't be used if you want any sort of persistence.

Answer 2

A temp table (#test) isn't actually called #test in the tempdb database. This is because every user on the system can create a table called #test. If you create a temp object the physical object in the tempdb database (found by looking at the sys.all_objects catalog view). In my case it was created as "#test_________________________________________________________________000000000003". Where if you create a physical table in the tempdb database it is called test, and only one use at a time can create the object, and if multiple users put data into a physical table called test then they will be able to access each others data. Where when you have the temp tables users can only access their own data and their own table.