I have a pretty large temp table (about 4.5 million rows) with no indexes, so it's stored in a heap. Let's call it
#TempTable1 has a BIT field called ToInclude which is set in a subsequent UPDATE query and uses a CASE statement based on other fields in the table to set the value appropriately.
UPDATE #TempTable1 SET ToInclude = CASE... query takes a full minute to execute. But instead of doing an UPDATE, if I
SELECT *, CASE...END AS ToInclude INTO #TempTable2 that only takes about five seconds, even though it's the same exact CASE statement I'm using in either query.
Here are the TIME and IO statistics for each instance.
I see in the first case (for the UPDATE query) SQL uses the Worktable. I've never heard of this concept before, but more so I'm interested in why it goes this route. It looks like in the first case, it takes an extra 15 million logical reads too, not sure why.
Also, I've tested this multiple times, switching the order of which query I run first, and dropping
#TempTable1 between each test, to ensure I'm not experiencing a difference in memory caching. These results continue to be repeatable.