Why does my ORDER BY sort the two tables sorted before the EXCEPT (slow) and not after? (fast)?
**SQL server 2008 R2 query optimizer puzzle**

We have two tables, both containing 9 million rows. 70.000 rows are different, the others are the same.

This is fast, 13 seconds,

    select * from bigtable1
    except select * from similar_bigtable2

This sorts the output and is also fast, 13 seconds as well,

    select * into #q from bigtable1
    except select * from similar_bigtable2
    select * from #q order by sort_column

While this is enormously slow:

    ;with q as (
    	select * from bigtable1
    	except select * from similar_bigtable2
    )
    select * from q order by sort_column

And even a "trick" that I sometimes use to hint SQL Server that it needs to precalculate a certain part of the query before it moves on, doesn't work and results in slow query as well:

    ;with q as (
    	select top 100 percent * from bigtable1
    	except select * from similar_bigtable2
    )
    select * from q order by sort_column

Looking at the query plans the reason is not hard to find:

![Query plan][1]
![Query plan with ORDER BY][2]

SQL Server places two sorts of 9 million rows *before* the hashmatch, while I would prefer it to have added only one sort of 70.000 rows *after* the hashmatch.

So the question: how can I instruct the query optimizer to do that?

  [1]: http://i.stack.imgur.com/GJUUk.jpg
  [2]: http://i.stack.imgur.com/KRcuy.jpg
Why does my ORDER BY sort the two tables sorted before the EXCEPT (slow) and not after? (fast)?
===

**SQL server 2008 R2 query optimizer puzzle**

We have two tables, both containing 9 million rows. 70.000 rows are different, the others are the same.

This is fast, 13 seconds,

    select * from bigtable1
    except select * from similar_bigtable2

This sorts the output and is also fast, 13 seconds as well,

    select * into #q from bigtable1
    except select * from similar_bigtable2
    select * from #q order by sort_column

While this is enormously slow:

    ;with q as (
    	select * from bigtable1
    	except select * from similar_bigtable2
    )
    select * from q order by sort_column

And even a "trick" that I sometimes use to hint SQL Server that it needs to precalculate a certain part of the query before it moves on, doesn't work and results in slow query as well:

    ;with q as (
    	select top 100 percent * from bigtable1
    	except select * from similar_bigtable2
    )
    select * from q order by sort_column

Looking at the query plans the reason is not hard to find:

![Query plan][1]
![Query plan with ORDER BY][2]

SQL Server places two sorts of 9 million rows *before* the hashmatch, while I would prefer it to have added only one sort of 70.000 rows *after* the hashmatch.

So the question: how can I instruct the query optimizer to do that?

  [1]: http://i.stack.imgur.com/GJUUk.jpg
  [2]: http://i.stack.imgur.com/KRcuy.jpg