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