Grant Fritchey (1/31/2013)

---

SQLSACT (1/31/2013)

---

Thanks

Basically 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.

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