I saw a question the other day, questioning why they’re creation of temporary tables was so slow. What they were doing was (a much more complicated version of) this:
SELECT soh.SalesOrderID, sod.SalesOrderDetailID, soh.SalesOrderNumber INTO #MyTempTable FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID WHERE 1 = 0;
Now, my immediate response, and no, I didn’t type it, was, “Hey, you’re not “creating” temporary tables. You’re using SELECT…INTO.”
Let’s be fair. That is a method to create temporary tables. Also, that method has some advantages. Biggest one being, you don’t have to know, or define, the data structure. You get it for free.
It does come down to one thing though. Why is “WHERE 1=0” slow?
Math may be weird these days, but in good old SQL Server, one (1) does not equal zero (0). Period.
So, our query above will not return any rows. So why is it slow? Well, let’s change gears a little. Here’s another query:
CREATE TABLE #MyTempTable ( SalesOrderID INT, SalesOrderDetailID INT, SalesOrderNumber NVARCHAR(25) );
And this is the execution plan from that query:
And here is the execution plan from the INSERT…SELECT query:
Now, what you didn’t get was a plan for the SELECT part of the query. Why? Because SQL Server knows that 1=0 is going to result in no rows. Instead, it builds the table of constants, that’s what a Constant Scan represents, which is just placeholders for columns. If you look, the output of the Constant Scan is this:
It’s just defining the data that would be inserted, if data was to get moved. However, since no data is being moved, all you need is what you see. It’s still running an INSERT, but for zero data. Performance for the SELECT…INTO is about 3.3ms with 198 reads on average. The simple CREATE query is 2.5ms on average with 145 reads.
Why is WHERE 1=0 slower? It’s doing more work.
Yeah, I know this one is easy to see, but you’d be surprised. People just think that everything gets figured out behind the scenes such that, two approaches, both with identical results, are done the same way. However, as we see above, that’s just not true. All approaches, even if they end in the same results, are just not equal. Is that inconvenient? Yeah, maybe. However, it’s still true.