User-defined functions - best practice suggestions.

User-defined functions - best practice suggestions.

• lkurts

  Advanced: No

  Hard to figure out: No

  Hard to get right / risky: Maybe so!

   

  Problems:

      A) Scalar UDFs can be VERY slow in some cases, and their cost does not show up in execution plans.  Result: you may think your UDF is incredibly efficient, when in fact it is sucking your server's life away.

   

      B) Multistatement table-valued UDFs.  Same problem as scalar UDFs.

   

  Solution:

      A) Favor inline table-valued UDFs over multistatement UDFs

   

      B) Test, test, test.

   

  UDFs are a great way to encapsulate logic, but just like any other tool they have their caveats and should be used with care.

   

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <Precipitous@discussions.microsoft.com> wrote in message news:16b25a75-15bc-436a-a809-cfcc6ae106f9@discussions.microsoft.com...

  I've got a couple guys on my team who seem to think user defined functions are a "risky" feature in SQL 2000. I've been using them for years without problems. Yeah, this one of those loaded prove me right or flame me questions ... whatever. I promise not to consider posts on MSDN forums authoritative answers to this practice question. I'm just trying to see where people stand. What I'm interested in knowing is:

  Do you consider UDF's in SQL advanced, hard to figure out, hard to get right or risky

  Any cases where they've caused you more problems than other MS SQL features

  Random hints on best-practice for UDF's - where do they spead things up, slow things down

  Reasons that they are better than sliced bread / cheese spread

  
  

• floatping

  I think it is a question of when to use UDFs. You should simply avoid UDFs in SELECT statements or DMLs since there are lot of performance implications. For example, if you use scalar UDFs in UPDATE statement the query optimizer can introduce additional spools in the plan which will be very expensive. The other problem is the lack of statistics for scalar UDFs and multi-statement table-valued functions. In SQL Server 2005, you can use the SCHEMABINDING option on scalar UDFs to give better hints to the query optimizer as to the nature of the UDF (does it do data access or not for example). But you will always get the best performance by inlining the expression in the UDF for example. So it is best to avoid using scalar UDFs in cases where you are trying to simplify usage of an expression multiple times in a SELECT statement. This can really prove to be a major performance problem. To summarize:

  1. Use scalar UDFs in SELECT or DMLs sparingly (in predicates or select list or SET clause of UPDATE)

  2. Using scalar UDFs to avoid repeating simple or even complex expressions that are easily inlineable in queries or DMLs will cause performance problems

  3. Use multi-statement table-valued functions carefully. The lack of statistics means that the query optimizer often makes a guess as to the cardinality of the rows returned and plan choice can be bad

  4. Using SCHEMABINDING option for UDFs in SQL Server 2005 can provide some performance advantages. See http://blogs.msdn.com/sqlprogrammability/archive/2006/05/12/596424.aspx

  5. Using scalar UDFs in procedural code to simplify logic is better than using those in set-based statements

  6. Avoid doing data access or external process activation from within UDFs since they can cause performance problems or unexpected results also depending on their usage

  7. If there are built-ins available to do a particular function then use those in queries or DMLs as opposed to wrapping them in a TSQL scalar UDF

  8. Consider replacing TSQL scalar UDFs that perform complex computations with SQLCLR UDFs. See http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnsql90/html/sqlclrguidance.asp

  
  
  

• Jean Fuertes

  Thanks, this is very informative.

  We have conservative DBA's and lots of legacy, and haven't moved to SQL 2005 yet. I assume the general rules carry over.

  Does SCHEMABINDING have the same affect in MS SQL 2000 I usually schemabind databases that I own totally, but tend not to on shared databases as it confuses the heck out of other people. It's a style thing: I prefer to get annoying error messages early than fix something later.

  Does the lack of stats also apply to inlines

  
  

• Erratis

  The schemabinding optimization for UDFs is new to SQL Server 2005. Inlined table-valued functions are similar to views. The query optimizer has access to the stats on all the base tables involved so it can generate an efficient plan.
  
  
• User-defined functions - best practice suggestions.