title: sql need performant polymorphic inline table value function
body: <p>I need to achieve performant polymorphic inline table valued functions in sql in some way shape or form.</p>
  
  <p>My current approach is as follows.  It does NOT perform well.  In my actual code I have on the order of 50-100 different types.</p>
  
  <pre><code>create table things (
      id int identity primary key,
      typecode int
  )
  
  create function foo_type1()
  returns table
  as return
      select id, v = 'blah1' 
      from things
  
  create function foo_type2()
  returns table
  as return
      select id, v = 'blah2' 
      from things
  
  create function foo_type3()
  returns table
  as return
      select id, v = 'blah3' 
      from things
  
  
  create function foo_poly()
  return table
  as return    
  select
      f.*
  from things t
  join (
      select typecode=1, * from foo_type1()
      union all select typecode=2, * from foo_type2()
      union all select typecode=3, * from foo_type3()
  ) f on f.typecode = t.typecode
  </code></pre>
  
  <p>My typical query is for a small subset of rows, eg</p>
  
  <pre><code>select * from foo_poly() where id in (123, 912, 2312)
  select * from foo_poly() where id = 123
  </code></pre>
  
  <p>My <code>things</code> table has around 2.5 million (small) rows.</p>
  
  <p>My actual values (<code>v</code> columns) are more complex than selecting a constant and must be computed dynamically.</p>
  
  <p>The select query is taking way too long (~10 seconds).</p>
  
  <p>I have tried a bunch of things (left joins+coalesce was WAY worse) but am out of ideas.</p>