Getting around not having inline scalar functions

It is well-known that Scalar functions (still) perform poorly (as of 2012) when used in selects returning a large number of rows.  Although it’s often advantageous to encapsulate certain logic, too often it’s not worth the performance hit to use a scalar function.
Below I demonstrate a technique using an inline table function that, while not pretty, does provide encapsulation WITHOUT the scalar function performance impact. In this example I encapsulate a formula to display only YYYY-MM from a date field.

CREATE FUNCTION itf_YYYY_MM(@Date DATETIME) RETURNS TABLE
RETURN SELECT CAST(YEAR(@Date) AS CHAR(4)) + '-'
      + RIGHT('0' + CAST(MONTH(@Date) AS VARCHAR(2)),2) AS [YYYY-MM]; 

Unfortunately this can’t simply be used like a scalar function. IOW this does NOT work:

 SELECT itf_YYYY_MM(MyDate) [YYYY-MM] FROM DataSource; 

You must jump thru a bit of a hoop.

 SELECT (SELECT [YYYY-MM] FROM itf_YYYY_MM(MyDate)) [YYYY-MM] FROM DataSource; 

The extra code, while not pretty, does in fact produce exactly the same plan as imbedding the original formula into the query. Thus our goal of encapsulation is met. And, generally speaking, any single statement scalar formula can be inlined using this technique.

Leave a comment