How to avoid Functions from a Performance Stand-point in Azure SQL Database?

I read this article quite recently on the performance issues to do with Functions.

I'm currently in the development phase of a new database on the Azure SQL Database platform. It won't go live for a new months yet. I'm using a variety of scalar-valued functions with one being to convert UTC to Local Date where it is specified in the Config table, e.g. AUS Eastern Standard Time

The function is called as the following:

SELECT
    dbo.fnGetLocalDate(DateColumn) as DateColumn,
FROM
    table

Does anyone have any suggestions on how to avoid functions in cases such as this? I find them useful for code reuse and I'm not sure how to avoid it here.

I'm also wondering whether with vNext fixing the performance issues with Functions in the Azure platform that I'm better off continuing to work with functions.

CREATE FUNCTION [dbo].[fnGetLocalDate]
(
    @DateToConvert datetimeoffset = NULL
)
RETURNS datetimeoffset
AS
BEGIN

    DECLARE @TimeZone varchar(50)

    RETURN 
    CASE 
        WHEN @DateToConvert is NULL then NULL
    ELSE 
        CONVERT(datetimeoffset, @DateToConvert AT TIME ZONE (SELECT Value FROM LookUp.Config WHERE Property = 'TimeZone'))
    END

END

You can rewrite it as an inline TVF returning a single column and row and CROSS APPLY it to get the benefits of inlining now (parallelism, no overhead of switching execution contexts, holistic query costing and optimisation) without having to wait for the work done on inlining of Scalar UDFs to get released.

So your function definition would be

CREATE FUNCTION [dbo].[fnGetLocalDate] (@DateToConvert DATETIMEOFFSET = NULL) 
RETURNS TABLE 
AS 
    RETURN 
      SELECT CONVERT(DATETIMEOFFSET, @DateToConvert AT TIME ZONE 
                                     (SELECT Value 
                                      FROM   LookUp.Config 
                                      WHERE  Property = 'TimeZone')) AS 
                    LocalDate

With example usage

SELECT o.name, L.LocalDate
FROM sys.objects o
CROSS APPLY [dbo].[fnGetLocalDate](o.modify_date) AS L

You don't really need the CASE as it will return NULL on NULL input anyway and may get better plans without it

StackExchange Database Administrators Q#197142, answer score: 6