TheSQLGuru (1/16/2014)
GilaMonster (1/16/2014)
TheSQLGuru (1/16/2014)
IIRC explicit drops aren't even honored any longer...They are, they always have been. Drop a temp table and you can't access it any longer. Essential when testing code before putting into a procedure. 'cannot create #test because it already exists' is annoying.
Now, the engine may choose to cache that temp table (strip out the metadata and just leave a couple of allocated pages), but it can do that with automatic drops on session close anyway.
The caching is what I meant - the object "stays around" in tempdb, although yes as you say you can't actually access it any longer.
The 'object' without it's name, without most of its metadata and without all but two (I think) pages. It's pretty much a drop, what SQL chooses to do behind the scenes is up to it, also it won't always be cached.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability