Can I trust Execution plans ?

Question

I have these Queries:

With CTE(comno) as
(select distinct comno=ErpEnterpriseId from  company)
select id=Row_number() over(order by comno),comno from cte

select comno=ErpEnterpriseId,RowNo=Row_number() over (order by erpEnterpriseId) from company group by ErpEnterpriseId


SELECT erpEnterpriseId, ROW_NUMBER() OVER(ORDER BY erpEnterpriseId) AS RowNo 
FROM 
( 
    SELECT DISTINCT erpEnterpriseId 
    FROM Company 
) x 

All three of them returns identical cost and actual execution plans..why and how so ?

Answer 1

Simple, the optimizer is probably turning all your statements into the same statement.

Answer 2

It's all down to the query optimizer - that will by trying to optimize the query you enter into the most efficient execution plan (i.e several different queries could be optimized down to the SAME statement that is estimated to be most efficient).

The main thing you should do when trying to optimise a query and find which one performs the best, is to just try them and compare performance. Run an SQL profiler trace to see what the duration/reads is for each version. I usually run each version of a query 3 times to get an average to compare. Each time, clearing the execution plan and data cache down to prevent skewed results.

It's worth having a read of this MSDN article on the optimizer.

Answer 3

Just like in English, in which there are many ways to say the same thing, all three of those queries are asking for the same data. The SQL Engine (the query optimizer) knows that and is smart enough to know what you are asking.

Even more appropriately, the engine has information that you don't (or likely don't know) - how the data is organized and indexed. It uses this information to make it's own decision about what the BEST way to get the data is, and that's what it is doing.

Although there are ways to override the optimizer, unless you really know what you are doing, you will probably only hurt performance. So your best option is to write the queries in whatever way make most sense to you (or other humans) for readability and maintainability.