Database Reference
In-Depth Information
go
select c.ClientName, o.OrderId, o.OrderDate, o.OrderNumber, o.Amount
from dbo.Clients c cross apply dbo.udfClientOrders(c.ClientId) o
where c.ClientId=1
■
the
APPLY
operator invokes a table-valued function for every row from the outer table. the table-valued
function can accept values from the row as the parameters. sQL server joins the row from the outer table with every row
from the function output, similar to the two-table join.
CROSS APPLY
works in a similar manner to the inner join. thus if
function does not return any rows, the row from the outer table would be excluded from the output.
OUTER APPLY
works
in a similar way to the outer join.
Note
Even though there is enough statistical information to estimate the number of orders correctly for the client with
ClientId=1
, the estimated number of rows is incorrect. Figure
10-8
demonstrates this. This behavior can lead to a
highly inefficient execution plan when functions return the large number of rows.
Figure 10-8.
Execution plan of the query with the multi-statement table-valued function
As you probably noticed, all of the functions were created with the
schemabinding
option. While it is not
required, specifying this option can help in several ways. It
binds
the function with the objects they reference, and
it prevents any metadata changes that can potentially break the code. When the function does not access the data,
schemabinding
forces SQL Server to analyze the function body. SQL Server will know that function does not access
any data, which in some cases helps to generate more efficient execution plans.
