Derived Table, CTE and INNER JOIN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Derived Table, CTE and INNER JOIN

Author

Topic

vsvenugs
Starting Member

1 Post

Posted - 2011-07-21 : 15:14:53

Derived Table

USE AdventureWorks2008R2;
GO
SELECT c.CustomerID, s.SalesOrderID
FROM Sales.Customer AS c
INNER JOIN (SELECT SalesOrderID, CustomerID
FROM Sales.SalesOrderHeader) AS s ON c.CustomerID = s.CustomerID;

Common Table Expression

USE AdventureWorks2008R2;
GO
WITH orders AS (
SELECT SalesOrderID, CustomerID
FROM Sales.SalesOrderHeader
)
SELECT c.CustomerID, orders.SalesOrderID
FROM Sales.Customer AS c
INNER JOIN orders ON c.CustomerID = orders.CustomerID;

Regular Inner Join

select c.CustomerID, s.SalesOrderID
from Sales.Customer c
inner join Sales.SalesOrderHeader s
on c.CustomerID = s.CustomerID

All the above queries returned the same number of rows and actual execution plan also looks the same. I am new to T-SQL. Would like to know which query is efficient. Pleas explain.

Thanks,
Venu

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-21 : 15:42:27

If the ACTUAL execution plans are the same, they are all equally efficient - they are all processed by the server the same way.

I don't know if one can make a general rule out of it - you probably cannot. I have seen cases where subqueries perform better than CTE's and may be even vice versa.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-21 : 17:05:36

A good developer will ask those questions, look at the plans, look at the IO, statistics, etc. Keep it up

Some general rules of thumb would include reducing the number of rows as early as possible. Derived tables can achieve that nicely when they significantly reduce the rows - ie 10bil down to 10. However, derived tables may prevent the use of indexes that would otherwise be available with direct JOINs. CTEs seem to be a very nice convenience for the developer especially when recursion is required. But some disadvantages can be when the statement deals with a lot of data and/or very complex statements. I've seen some odd extreme slowness with "over CTEing"

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-21 : 18:38:06

quote:

derived tables may prevent the use of indexes that would otherwise be available with direct JOINs.

I haven't seen that be the case. Do you have a quick example?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-22 : 11:35:33

Tara,
I tried (quickly) to create an example and couldn't. (at least not without cheating)
I created a simple statement from two tables and deliberately listed them in an order I wouldn't normally use. I then copied the statement and replaced the first table with a (select * from <firstTAble>) as d. If I used the OPTION (force order) clause the index was still used by the first statement with a direct join but not for the second statement with dervided table. again cheating with the force order option.

I'm sure I have seen the behavior but it could have been a case where an overly complex query or out of date statistics resulted in the optimizer using a bad plan. And that by simplifying the statement which coincidentally involved removing a derived table, resulted in the "correct" plan.

I have to admit that I used to think of derived tables like intermediate result sets as opposed to what could more accurately be thought of as in-line views. Of course execution plans for statements with Views (as with derived tables) are actually using the underlying tables.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources