Tony Rogerson's ramblings on SQL Server

Derived tables really are a wonderful tool in our tool chest when it comes to writing highly scalable SQL but only when they are used correctly. I'm currently writing my presentation for SQLBits and whilst digging around on the Derived Tables demos it jogged my memory of this problem but on 2005 I could no longer repro it - I thought I was going mad, but no - no madness they've just improved the 2005 implementation and remove the odd problem.

In SQL Server 2000 if you use a UDF in derived table and later refernce that column, the chances are the UDF will be executed again rather than using the value returned in the derived table.

Best seen as an example...

drop function dbo.fn_strip_time

go

--  Because we can't use CURRENT_TIMESTAMP in a UDF

create view vw_NOW as select NOW = current_timestamp

go

create function dbo.fn_strip_time(

    @value datetime

) returns datetime

as

begin

    declare @val2 datetime

    set @val2 = cast( convert( char(8), @value, 112 ) as datetime )

    --  force a delay of 3 seconds

    declare @now datetime

    select @now = now from vw_now

    while ( select now from vw_now ) <= dateadd( second, 3, @now )

        set @val2 = @val2

    return( @val2 )

end

go

--  Yes, this takes 3 seconds

print dbo.fn_strip_time( current_timestamp )

go

create table #tb(

    somedate    datetime not null

    )

insert #tb values( current_timestamp )

select * into #tb2 from #tb

select dateadd( day, 1, stripped_date_col )

from (

    select dbo.fn_strip_time( somedate ) as stripped_date_col

    from #tb

    ) as dt

    inner join #tb2 t on t.somedate >= dt.stripped_date_col and t.somedate < current_timestamp

Notice that final SQL statement takes 6 seconds on SQL 2000 build 2187, on 2005 SP2 build 3159 it takes just 3 seconds which is what we want.