Views - Pro SQL Server Internals - page 179

Listing 9-4. Views and Joins: vOrders view creation
create view dbo.vOrders(OrderId, Clientid, OrderDate, OrderNumber, Amount, ClientName)
as
select o.OrderId, o.ClientId, o.OrderDate, o.OrderNumber, o.Amount, c.ClientName
from
dbo.Orders o join dbo.Clients c on
o.Clientid = c.ClientId;
This implementation is very convenient for developers. By referencing the view, they have complete information
about the orders without worrying about the underlying join. When a client application wants to select a specific
order, it could issue the select, as shown in Listing 9-5, and get the execution plan, as shown in Figure 9-1 .
Listing 9-5. Views and Joins: Selecting all columns from vOrders view
select OrderId, Clientid, ClientName, OrderDate, OrderNumber, Amount
from dbo.vOrders
where OrderId = @OrderId
Figure 9-1. Execution plan when selecting all columns from the view
This is exactly what you are expecting. SQL Server replaces the view with an underlying query that selects data
from the Orders table, joining it with the data from the Clients table. Although, if you run the query that returns
columns only from the Orders table, as shown in Listing 9-6, you would have slightly unexpected results and the
corresponding execution plan, as shown in Figure 9-2 .
Listing 9-6. Views and Joins: Selecting columns from the Orders table using vOrders view
select OrderId, OrderNumber, Amount
from dbo.vOrders
where OrderId = @OrderId