title: Why is a UDF so much slower than a subquery?
body: <p>I have a case where I need to translate (lookup) several values from the same table. The first way I wrote it, was using subqueries:</p>
  <pre><code>SELECT
      (SELECT id FROM user WHERE user_pk = created_by) AS creator,
      (SELECT id FROM user WHERE user_pk = updated_by) AS updater,
      (SELECT id FROM user WHERE user_pk = owned_by) AS owner,
      [name]
  FROM asset
  </code></pre>
  <p>As I'm using this subquery a lot (that is, I have about 50 tables with these fields), and I might need to add some more code to the subquery (for example, &quot;AND active = 1&quot; ) I thought I'd put these into a user-defined function <a href="http://en.wikipedia.org/wiki/User-defined_function" rel="noreferrer">UDF</a> and use that. But the performance using that UDF was abysmal.</p>
  <pre><code>CREATE FUNCTION dbo.get_user ( @user_pk INT )
  RETURNS INT
  AS BEGIN 
      RETURN ( SELECT id
               FROM   ice.dbo.[user]
               WHERE  user_pk = @user_pk )
  END
  
  SELECT dbo.get_user(created_by) as creator, [name]
  FROM asset
  </code></pre>
  <p>The performance of #1 is less than 1 second. Performance of #2 is about 30 seconds...</p>
  <p>Why, or more importantly, is there any way I can code in SQL server 2008, so that I don't have to use so many subqueries?</p>
  <h3>Edit:</h3>
  <p>Just a litte more explanation of when this is useful. This simple query (that is, get userid) gets a lot more complex when I want to have a text for a user, since I have to join with profile to get the language, with a company to see if the language should be fetch'ed from there instead, and with the translation table to get the translated text. And for most of these queries, performance is a secondary issue to readability and maintainability.</p>