Views - Pro SQL Server Internals - page 180

Figure 9-2. Execution plan when selecting columns that belong to the Orders table only
As you see, SQL Server still does the join even if you do not need ClientName data there. It makes sense: You
are using inner join in the view, and SQL Server needs to exclude the rows from the Orders table that do not have
corresponding rows in the Clients table.
How can you solve this problem and eliminate the unnecessary join? The first option is to use an outer join rather
than the inner one, as shown in Listing 9-7.
Listing 9-7. Views and Joins: vOrders2 view creation
create view dbo.vOrders2(OrderId, Clientid, OrderDate, OrderNumber, Amount, ClientName)
as
select o.OrderId, o.ClientId, o.OrderDate, o.OrderNumber, o.Amount, c.ClientName
from
dbo.Orders o left outer join dbo.Clients c on
o.Clientid = c.ClientId;
Now if you run select statement, as shown in Listing 9-8, you would have the execution plan without join,
as shown in Figure 9-3 .
Listing 9-8. Views and Joins: Selecting columns from the Orders table using vOrders2 view
select OrderId, OrderNumber, Amount
from dbo.vOrders2
where OrderId = @OrderId
Figure 9-3. Execution plan with left outer join
While it does the trick, outer joins restrict the choices of the query optimizer when generating execution plans.
Another thing to keep in mind is that you changed the behavior of the view. If you can have orders that do not belong
to clients in the system, then the new implementation would not exclude them from the result set. This can introduce
side effects and break other code that references the view and relies on the old behavior of the inner join. You must
analyze the data and subject area before implementing join elimination using the outer joins.
A better option is adding a foreign key constraint to the Orders table, as shown in Listing 9-9.