461
Chapter 18: Building User-Defi ned Functions
18
CROSS APPLY dbo.ufnProductOrderTotals(p.ProductID) t
ORDER BY t.TotalOrders DESC;Result:Name TotalOrders
-------------------------------------------------- -----------
AWC Logo Cap 8311
Water Bottle - 30 oz. 6815
Sport-100 Helmet, Blue 6743
Long-Sleeve Logo Jersey, L 6592
Sport-100 Helmet, Black 6532
Sport-100 Helmet, Red 6266
Classic Vest, S 4247...
The second form, the OUTER APPLY command, operates much like a left outer join. With
this usage, rows from the main query are included in the result set regardless of whether
the virtual table returned by the user-defi ned function is empty.Best Practice
The scalar function dbo.ufnGetOrderTotalByProduct and the inline table-valued function dbo
.ufnProductOrderTotals can produce the same result set. So what’s different? The scalar function
fi res once per row, whereas the inline table valued function is treated by the query optimizer much like
a view. There is a growing school of thought that because of this treatment by the query optimizer, it
is highly preferable to use inline table-valued functions instead of scalar functions wherever possible.Creating Functions with Schema Binding
All three types of user-defi ned functions may be created with the signifi cant added benefi t
of schema binding. Views may be schema bound; in this way, UDFs are like views — both
can be schema bound. This is one reason why you might choose a UDF over a stored proce-
dure because stored procedures cannot be schema bound.Schema binding prevents the altering or dropping of any object on which the function
depends. If a schema-bound function references TableA, then columns may be added to
TableA, but no existing columns can be altered or dropped, and neither can the table itself.Although it is true that stored procedures cannot be schema bound, there is a new feature in SQL
Server 2012 called Result Sets that can guarantee the structure of the returned results at run time.
Check out Chapter 16, “Programming with T-SQL” for more information.c18.indd 461c18.indd 461 7/30/2012 5:41:46 PM7/30/2012 5:41:46 PM
http://www.it-ebooks.info