Following the trend of my previous post I found this explanation on Kimberly Tripp’s blog and it sure cleared out a few things for me:
…understanding [of] TempDB – what goes there?
- Internal temporary objects needed by SQL Server in the midst of other complex operations. For example, worktables created by a hash aggregate will be stored in TempDB or interim tables uses in hash joins (almost anything that shows as “hash” something in your query plan output is likely to go to tempdb).
- User objects created with either # (for local temporary objects), ## (globabl temporary objects) or @ (table variables)
- # = Local temporary object
Local temp objects are objects accessible ONLY in the session that created it. These objects are also removed automatically when the session that created it ends (unless manually dropped). - ## = Globabl temporary object
Global temporary objects are objects that are accessible to ANYONE who can login to your SQL Server. They will only persist as long as the user that created it lasts (unless manually dropped) but anyone who logs in during that time can directly query, modify or drop these temporary objects. These objects are also removed automatically when the session that created it ends (unless manually dropped) OR if being used by another session when the session that created it ends, when the session using it finishes using it (and it’s only as long as any locks are held). If other sessions need more permanent use of a temporary object you should consider creating a permanent objects and dropping it manually. - @ = User-defined Table Variable
User-defined Table Variables were introduced in SQL Server 2000 (or, wow – was it 7.0?) and provide an alternative to temporary tables by allowing you to create a variable defined as type TABLE and then you can populate and use it in a variety of ways. There has been A LOT of debate over whether or not you should always use table variables or always use temp tables. My response is that I ALWAYS avoid the word always! My point is that table variables are NOT always better nor are temp tables always better. There are key uses to each. I tend to like temp tables in scenarios where the object is used over a longer period of time – I can create non-key indexes on it and it’s more flexible to create to begin with (SELECT INTO can be used to create the temp table). I also have the ability to use the temporary table in nested subprocedures because it’s not local to the procedure in which it was created. However, if you don’t need any of those things then a table variable might be better. When it is likely to be better – when you have smaller objects that don’t need to be accessed outside of the procedure in which it was created and when you only need KEY indexes (a table variable ONLY supports the indexes created by a create table statement – meaning PRIMARY KEY and UNIQUE KEY).
- # = Local temporary object
The origininal post is here
I’ve been following up the series SQLSkills is publishing on Sql Server 2005…
More will come up soon folks!