Adding inner query is not changing the execution plan.

Question

Consider the following queries.

select * from contact where firstname like '%some%'

select * from 
    (select * from contact) as t1 
where firstname like '%some%'

The execution plans for both queries are same and executes at same time. But, I was expecting the second query will have a different plan and execute more slowly as it has to select all data from contact and apply filter. It looks like I was wrong.

I am wondering how this is happening?

Database Server : SQL server 2005

Answer 1

The "query optimiser" is what's happening. When you run a query, SQL Server uses a cost-based optimiser to identify what is likely to be the best way to fulfil that request (i.e. it's execution plan). Think about it as a route map from Place A to Place B. There may be many different ways to get from A to B, some will be quicker than others. SQL Server will workout different routes to achieve the end goal of returning the data that satisfies the query and go with one that has an acceptable cost. Note, it doesn't necessarily analyse EVERY possible way, as that would be unnecessarily expensive.

In your case, the optimiser has worked out that those 2 queries can be collapsed down to the same thing, hence you get the same plan.