title: User-defined ranking / analytic functions in SQL Server 2008
body: <p>I'm planning a data warehouse migration to SQL Server 2008, and trying to think of ways to replicate the LAG, LEAD, FIRST_VALUE and LAST_VALUE analytic functions from Oracle in SQL Server 2008. They are not included in SQL Server 2008, although the basic machinery for windowed analytic functions is (e.g. ROW_NUMBER, RANK and DENSE_RANK are all present).</p>
  
  <p>For these functions, it is possible to achieve the same function by creating a subquery that assigns each row a number using ROW_NUMBER and then doing self-joins of that query to find related rows with nearby row numbers (for LAG and LEAD), or a row number of 1 (for FIRST_VALUE).</p>
  
  <p>I expect that doing the self-joins would detract from the efficiency of an operation: but I don't yet have a SQL Server to test this. So without having actually assessed the performance, I'm wondering whether there is a better workaround that avoids the self-joins.</p>
  
  <p>Looking at the documentation for <a href="http://msdn.microsoft.com/en-us/library/ms190678.aspx" rel="nofollow noreferrer">user-defined aggregate functions</a>, it is conceivable that the same code structure could be used to provide user-defined analytic functions.</p>
  
  <p>So my question is: can you add an OVER() clause after a user-defined aggregate function to have it called as an analytic function?</p>
  
  <p>If so, is the Terminate() method called once per row? Is there anything special needed to ensure that rows are sent to your UDF in the ordering specified in the OVER() clause?</p>