Does where 1=2 get called for every row?

Question

I have some sprocs that need a temp table. In order not to hardcode the column types (which are varchar with some length) so I don't have to change the declarations when the reference table schema changes (i.e. fields become longer) I do this (instead of a create table call):

select orderId
into #sometmptbl
from orders
where 1=2

However, when you do a showplan on this it actually seems to be going to the table/index:

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
    The type of query is CREATE TABLE.

STEP 2
    The type of query is INSERT.
    The update mode is direct.

    FROM TABLE
        orders
    Nested iteration.
    Index : orders_idx1
    Forward scan.
    Positioning at index start.
    Index contains all needed columns. Base table will not be read.
    Using I/O Size 2 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.
    TO TABLE
        #sometmptbl
    Using I/O Size 2 Kbytes for data pages.

Total estimated I/O cost for statement 1 (at line 1): 632082.

Does this mean 1=2 gets evaluated for every entry in the index? Is there a way to do this in a constant time?

Update:

Here's the actual I/O cost after the execute so it looks like the actual reads are indeed 0 so there's no performance impact:

Table: orders scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #sometmptbl_____00002860018595346 scan count 0, logical reads: (regular=1 apf=0 total=1), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total actual I/O cost for this command: 2.
Total writes for this command: 3
0 row(s) affected.

Answer 1

If you set statistics io on, you should see zero logical and physical reads. It may create a plan to scan the index, but it appears not to actually use it.

I would recommend NOT creating temp tables this way in a high volume production environment. There are system table locking issues, as well as a slight performance hit (your mileage may vary). (also the identity attribute of a column gets carried over into the temp table).

As a shortcut - I do the 1=2 into an explicit tempdb..MyScratchTable, and then use RapidSQL (or some other tool), to generate the DDL from that scratch table.

If its a varchar, there shouldn't be any reason why you can't standardize the column length on the max value and just use that everwhere.

Answer 2

What does select orderId from orders where 1=2 give you?

It may have chosen the index to read the datatypes etc, whereas a really trivial query (without INTO) should be optimised with no table/index access at all.