Missing Join Predicate and Cross joins

[Dan Holmes]

I am getting the "Missing Join Predicate" warning in my query plans. I do have a CROSS JOIN in the SQL. Am i always
going to get this warning because of that? It is bad in this case?

CREATE TABLE Trips (
TripID INT NOT NULL PRIMARY KEY
, PickupAddressID INT NOT NULL
, DropOffAddressID INT NOT NUL
)

CREATE VIEW TripStops
AS
SELECT *
FROM Trips
CROSS JOIN (SELECT 'P' UNION ALL SELECT 'D') AS Stops(StopType)

[Uri Dimant]

Dan
When have you seen it ?

I got nothing wrong with SET STATISTICS PROFILE ON (SQL Server 2005 (SP3))

"Dan Holmes" <dan.h...@routematch.com> wrote in message
news:%23oFc6$TCKHA...@TK2MSFTNGP03.phx.gbl...

[Tom Cooper]

Yes, you will always get the warning on a cross join where both sides of the
join produce more than one row. See
http://msdn.microsoft.com/en-us/library/ms175146%28SQL.90%29.aspx

It is just a warning that the query may produce many rows. If the two sides
of a cross join each produce 1,000 rows, then the cross join will produce
1,000 X 1,000 or 1,000,000 rows.

In your case, the left side of the cross join only has 2 rows, so I don't
think that is any problem.

Tom

"Dan Holmes" <dan.h...@routematch.com> wrote in message
news:%23oFc6$TCKHA...@TK2MSFTNGP03.phx.gbl...

[Russell Fields]

As Tom says, you can ignore this message in your case. If you have reasons
why you would rather not raise this event, you could always do the following
in your case.

CREATE VIEW TripStops
AS
SELECT *, 'P' AS StopType
FROM Trips
UNION ALL
SELECT *, 'D' AS StopType
FROM Trips

FWIW,
RLF

"Dan Holmes" <dan.h...@routematch.com> wrote in message
news:%23oFc6$TCKHA...@TK2MSFTNGP03.phx.gbl...

[Uri Dimant]

Rusell
Do you get this message while running a Profiler?

"Russell Fields" <russel...@nomail.com> wrote in message
news:uaMAFdUC...@TK2MSFTNGP04.phx.gbl...

[Russell Fields]

Uri,

Yes. It is one of the Errors and Warnings events. It is also one thing
tracked in the SQL Server default trace.

As you noted, the code works just fine. It does, however, raise the event.
(I do not see any problem with ignoring it in this case. My comment was
just a way to avoid adding some noise to the trace, IF that matters to
someone.)

RLF

"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:Oij%23cmUCK...@TK2MSFTNGP02.phx.gbl...