title: Function columns cached
body: <p>It looks like <code>select *</code> in a UDF is dangerous.  Consider this script:</p>
  
  <pre><code>create table TestTable (col1 int, col2 varchar(1))
  insert into TestTable values (123, 'a')
  go
  
  create function TestFunction
  (
      @param1 bit
  )
  returns table
  as
  return
  (
      select * from TestTable
  )
  go
  
  select * from TestFunction(0)
  
  alter table TestTable
  add col3 varchar(1)
  
  select * from TestFunction(0)
  
  drop function TestFunction
  drop table TestTable
  go
  </code></pre>
  
  <p>You will get two result sets, both with the same number of columns, even though I added <code>col3</code>.  If the table is recreated an an extra column is inserted in the middle, everything will shift one column over, showing the data under the wrong column name.  In other words, the columns will stay the same, but the data has an extra column.</p>
  
  <p>I wasn't able to find any information about this, but it seems to me the only way to avoid this is to always specify your columns in a function.</p>
  
  <p>So my question is, what exactly does a UDF cache?  It seems output columns are--anything else?  Also, any way to still use <code>select *</code> but prevent this problem?  Thanks.</p>