464
Part IV: Programming with T-SQL
Result:ProductID ProductName TotalOrders
----------- ----------------------------- -----------
712 AWC Logo Cap 8311
870 Water Bottle - 30 oz. 6815
711 Sport-100 Helmet, Blue 6743
715 Long-Sleeve Logo Jersey, L 6592
708 Sport-100 Helmet, Black 6532
707 Sport-100 Helmet, Red 6266
864 Classic Vest, S 4247...
Multistatement table-valued, user-defi ned functions use tempdb to pass the table variable to the calling query. For
many applications this is not a concern, but for high-transaction applications, it could pose a performance problem.
Consider incorporating the code directly into the calling stored procedure.Best Practices with User-Defi ned Functions
Although user-defi ned functions add fl exibility to your T-SQL options, there are some seri-
ous performance drawbacks to the improper application of them. They are not a standard
replacement for subqueries, views, or stored procedures. In some cases, the user-defi ned
function provides benefi ts that make a strong case for its use, such as parameterization or
reusability. The trick is to use them properly.Maximizing Performance
From a query optimizer perspective, different types of user-defi ned functions are handled
differently. You’ve probably noticed that the examples provided for each of the three types
of UDF, scalar, inline, and table-valued, all produced essentially the same result set. This
was by design so that you could illustrate that in quite a few cases; you can defi ne your
function as any one of the three types. If you choose to use a user-defi ned function to
encapsulate your query logic, follow these basic guidelines:■ (^) Choose inline table-valued functions over multistatement table-valued functions
whenever possible.
■ (^) Even if it looks like you need a scalar function, write it as an inline table-valued
function; avoid scalar functions wherever possible.
■ (^) If you need a multistatement table-valued function, check to see if a stored proce-
dure might be the appropriate solution. This might require a broader look at query
structure, but it’s worth taking the time to do it.
c18.indd 464c18.indd 464 7/30/2012 5:41:48 PM7/30/2012 5:41:48 PM
http://www.it-ebooks.info