well, ideally, your scalar UDF can be converted to an inline table funciton instead.
Create FUNCTION fnAdjustDateTime_itv (@date datetime, @timezone int)
RETURNS table
AS
RETURN
SELECT DateAdd(hh,CASE
WHEN @timezone = 1 THEN -5
WHEN @timezone = 2 THEN -6
WHEN @timezone = 3 THEN -7
WHEN @timezone = 4 THEN -8
WHEN @timezone = 5 THEN -10
WHEN @timezone = 6 THEN -9
WHEN @timezone = 7 THEN -4
WHEN @timezone = 8 THEN -7
WHEN @timezone = 9 THEN -11
END,
@date) As ResultDate
GO
Create FUNCTION fnAdjustDateTime (@date datetime, @timezone int)
RETURNS datetime
AS
BEGIN
--
-- declare variables
--
declare @Dst bit
declare @offset int
declare @result datetime
--
-- Get the timezone data we will need
--
if @timezone = 1
Select @offset = -5, @Dst = 1
else if @timezone = 2
Select @offset = -6, @Dst = 1
else if @timezone = 3
Select @offset = -7, @Dst = 1
else if @timezone = 4
Select @offset = -8, @Dst = 1
else if @timezone = 5
Select @offset = -10, @Dst = 0
else if @timezone = 6
Select @offset = -9, @Dst = 1
else if @timezone = 7
Select @offset = -4, @Dst = 0
else if @timezone = 8
Select @offset = -7, @Dst = 0
else if @timezone = 9
Select @offset = -11, @Dst = 0
else
Select @offset = -6, @Dst = 1
--
-- Set result now
--
set @result = DateAdd(hh,@offset,@date)
return @result
END
GO
then the calling code gets changed to this:
select name, dbo.fnAdjustDateTime(create_date,1) from sys.objects
select name, fn.*
from sys.objects
CROSS APPLY dbo.fnAdjustDateTime_itv(create_date,1) fn
if you include actual execution palns and compare them, you will start seeing hwo the ITVF version is better; scalar functions usualyl get called once per row,
where an ITVF is treated like a set based operation
on this small dataset of using sys.objects, my results were the same; but on bigger datasets, the ITVF will be much more efficient.
Lowell