title: Performance of T-Sql query inside scalar user-defined function
body: <p>I recently reviewed a colleague's SQL Server function (in <code>SQL Server 2008</code>) where he had put a <code>T-SQL</code> query within a scalar user-defined function. That function was then used in the Select clause of a query.</p>
  
  <p>I thought that having a query inside a function would have terrible performance because I assumed each row returned would have to run the query - even though that query is optimised to query on an index.</p>
  
  <p>Here's an example of what I'm talking about:</p>
  
  <pre><code>create table [PERSON] (
  ID int primary key,
  FIRSTNAME NVARCHAR(100),
  MIDDLENAME NVARCHAR(100) null,
  LASTNAME NVARCHAR(100))
  GO
  
  INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
  VALUES (1, 'BOB', 'M', 'BLUE')
  
  INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
  VALUES (2, 'VALERIE', 'J', 'GREEN')
  
  INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
  VALUES (3, 'SIMON', 'D', 'RED')
  
  INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
  VALUES (4, 'LIONEL', 'W', 'BROWN')
  GO
  
  -- Scalar Function with T-SQL
  CREATE FUNCTION dbo.fn_FormatNameFromId(
              @pnPersonId int
              )
  Returns nvarchar(300)
  AS
  Begin
      return (Select FIRSTNAME + ' ' + MIDDLENAME + ' ' + LASTNAME
          From PERSON
          Where ID = @pnPersonId)
  End
  go
  
  -- Scalar Function without T-SQL
  CREATE FUNCTION dbo.fn_FormatNameFromValues(
              @psFirstName nvarchar(100),
              @psMiddleName nvarchar(100),
              @psLastName nvarchar(100)
              )
  Returns nvarchar(300)
  AS
  Begin
      return (@psFirstName + ' ' + @psMiddleName + ' ' + @psLastName)
  End
  go
  
  -- T-SQL within function
  select dbo.fn_FormatNameFromId(ID)
  from PERSON
  
  -- Pass values directly
  select dbo.fn_FormatNameFromValues(FIRSTNAME, MIDDLENAME, LASTNAME)
  FROM PERSON
  </code></pre>
  
  <p>When comparing the execution plans for both these selects, I could find no difference in the cpu or memory performance of the two functions. Even when scaling up the test to return tens of thousands of rows, there was no performance difference.</p>
  
  <p>Can anybody explain how or why the <code>fn_FormatNameFromId</code> is not causing a loss of performance?</p>