By Rick Bielawski
It is well-known that T-SQL scalar functions still (as of 2014) perform poorly when used against a large number of rows. Although encapsulating algorithms is a best practice, too often it’s not worth the performance hit that comes with using scalar functions to perform the encapsulation.
Scenario
The client stores event times in UCT(GMT), but reports will need to display the time in the report viewer’s local time. Translation depends on support for different time zones, but it’s also important to note that the day of the year when Daylight Savings Time starts and stops in the specific location is typically controlled by local legislation – it can change over time. Thus, the client wants to encapsulate the logic.
Issue
A scalar function might be ideal, were it not for the performance hit on certain large reports. The goal is to find a solution that doesn’t perform worse than hard-coding the logic into every query, while also avoiding the maintenance cost involved with maintaining the logic separately in every query.
Solution
The technique below demonstrates using an inline table function to provide encapsulation. This technique, while not as elegant, circumvents the scalar function’s performance impact. In this example I encapsulate a formula to display only YYYY-MM from a date field, but almost any formula that can be expressed in a single statement can be encapsulated in this way. The actual DST query is complex enough that I’ll cover it in another blog entry.
CREATE FUNCTION isf_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 inline scalar function can’t be used like a normal scalar function. In other words, 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 isf_YYYY_MM(MyDate)) [YYYY-MM] FROM DataSource;
The extra code, while not pretty, does in fact produce exactly the same query plan as imbedding the inlined logic directly in the query. Thus our goal of encapsulation is met without significant overhead.
Summary
Generally speaking, any single statement scalar formula can be inlined using this technique. Inline table functions, and even views, are often overlooked as valuable tools in creating high performance maintainable database solutions.
