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.

SQLSACT (1/31/2013)

---

Thanks



wrt to the memory/tempdb situation

Are work/hash tables first created in memory and spilled to tempdb if necessary?

Thanks

Grant Fritchey (1/31/2013)

---

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

All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time.

SQLSACT (1/31/2013)

---

Thanks

I understand the workspace memory area (Well, I hope I do). A query will request required memory and once the query has been granted that, if it needs to, it will request additional memory for processing a hash join for example, which might have to spill to disk it memory can't accomodate that additional memory. Correct?

I'm not experiencing any particular issue, I'm just trying to understand the behaviour.

Where I got thrown off a bit was here


Thanks