• 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!