Prev: get records from before 6 months ago
Next: Convert datetime field to interval
From: Dan Holmes on 30 Mar 2010 11:40
Under what circumstances will the optimizer not include a table in the join if the FK is trusted?

In this situation the INNER and LEFT both include the tbltrips table in the actual plan.

SELECT tt.*
FROM tbltripstemplate tt
INNER JOIN tbltrips t on t.id = tt.tripid
WHERE tt.id = 1 AND tt.tripdayofweek = 6
From: Gert-Jan Strik on 30 Mar 2010 16:50
Dan Holmes wrote:
>
> Under what circumstances will the optimizer not include a table in the join if the FK is trusted?
>
> In this situation the INNER and LEFT both include the tbltrips table in the actual plan.
>
> SELECT tt.*
> FROM tbltripstemplate tt
> INNER JOIN tbltrips t on t.id = tt.tripid
> WHERE tt.id = 1 AND tt.tripdayofweek = 6

Dan,

If "tbltripstemplate" has a foreign key relation that references
"tbltrips", then in this case the optimizer can and might omit the trips
table from the query plan.

If you don't see that, then maybe the Foreign Key is not trusted, or
maybe you are using an old(er) version of SQL Server.

You can find a demonstration and description of this behavior at
http://www.xs4all.nl/~gertjans/sql/example2/no-columns-from-autojoined-table.html

--
Gert-Jan
From: Uri Dimant on 31 Mar 2010 09:10
Dan
Tibor wrote a greate article on the subject visit on www.sqlblog.com

This is part of the article.Run the below query, SQL Server is smart enough
not to 'scan' SalesOrderHeader as there is no columns to be return
SELECT sd.SalesOrderID, sd.CarrierTrackingNumber
FROM Sales.SalesOrderHeader AS s

INNER JOIN Sales.SalesOrderDetail AS sd

ON s.SalesOrderID = sd.SalesOrderID

WHERE sd.OrderQty > 20

"Dan Holmes" <dan.holmes(a)routematch.com> wrote in message
news:%235emG9B0KHA.6124(a)TK2MSFTNGP02.phx.gbl...
> Under what circumstances will the optimizer not include a table in the
> join if the FK is trusted?
>
> In this situation the INNER and LEFT both include the tbltrips table in
> the actual plan.
>
> SELECT tt.*
> FROM tbltripstemplate tt
> INNER JOIN tbltrips t on t.id = tt.tripid
> WHERE tt.id = 1 AND tt.tripdayofweek = 6


 | 
Pages: 1
Prev: get records from before 6 months ago
Next: Convert datetime field to interval