SQLSACT (1/31/2013)
ThanksBasically correct. Although, it really only matters to the internals engine. For your purposes, both are dealt with the same way. According the to the Internals book, a hash table goes to what is called a work file, but that's treated in all ways the same as a work table. Further, when you have a hash join and you look at the I/O stats of the query, that hash is evidenced as something called a work table. So, I just treat the hash as a type of work table since the external evidence is exactly the same and MS labels it that way.
wrt to the memory/tempdb situation
Are work/hash tables first created in memory and spilled to tempdb if necessary?
Thanks
Yes & no. All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time. But the primary work is done in memory, if it can. Otherwise it puts the excess on the place it reserved on the disk.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning