One thing that I didn't find in the BOL What's New page is some of the new SQLCLR functionality in SQL Server 2008. The first one that intrigued me is support of multi-input user-defined aggregates. Suppose I wanted to implement Oracle's COVAR_POP aggregate, an analytic function that returns the population covariance between two expressions. The signature is COVAR_POP(expr1, expr2) and I want the signature to stay the same in SQL Server.

All that I need to do this is to use the "template" for a .NET UDAgg struct/class, replacing the Accumulate method that take one parameter with a 2-parameter method, like this:

You can create a view for this if you use this year and last year, because you can figure out this year and last year from the GETDATE function. A view is just like a "SQL macro", it will be expanded by SQL Server as though it were part of every query it participates in.

If you’d like to pass in an arbitrary year and return a set of data, this can be accomplished with a single-statement table valued function. This really is (even the classification of its query plan reads) a VIEW into which you can pass parameters.