title: Caching Function Results in SQL Server 2000
body: <p>I want to memoize function results for performance, i.e. lazily populate a cache indexed on the function arguments.  The first time I call a function, the cache won't have anything for the input arguments, so it will calculate it and store it before returning it.  Subsequent calls just use the cache.</p>
  
  <p>However, it seems that SQL Server 2000 has a stupid arbitrary rule about functions being "deterministic".  INSERTs, UPDATEs, and regular stored procedure calls are forbidden.  However, extended stored procedures are allowed.  How is this deterministic?  If another session modifies the database state, the function output will change anyways.</p>
  
  <p>I'm steaming mad.  I had thought I could make caching transparent to the user.  Is this possible?  I don't have the permissions to deploy extended stored procedures.</p>
  
  <p>EDIT:</p>
  
  <p>This limitation is still in 2008.  You can't call RAND, for God's sake!</p>
  
  <p>The cache would be implemented by me in the DB.  A cache is any data store used for caching...</p>
  
  <p>EDIT:</p>
  
  <p>There are no cases where the same arguments to a function will yield different results, outside of changes to the underlying data.  This is a BI platform, and the only changes come from scheduled ETL, at which time I would TRUNCATE the cache table.</p>
  
  <p>These are I/O intensive time series calculations, on the order of O(n^4).  I don't have the mandate to change the underlying table or indexes.  Also, a lot of these functions use the same intermediate functions, and caching allows those to be used.</p>
  
  <p>UDFs are not truly deterministic, unless they account for changes in database state.  What's the point?  Is SQL Server caching?  (Ironic.)  If SQL Server is caching, then it must be expiring on changes to tables that are schema bound.  If they're schema bound, then why not bind tables that the function modifies?  I can see why procs aren't allowed, although that's just sloppy; just schema bind procs.  And, BTW, why allow extended stored procs?  You can't possibly track what those do to ensure determinism!!!  Argh!!!</p>
  
  <p>EDIT:</p>
  
  <p>My question is:  Is there any way to lazily cache function results in a way that can be used in a view?</p>