Does SQL re-evalulate the function for every row or just once, converting it to a constant

Question

if I have a function

ALTER FUNCTION [dbo].[GetCycleDate]
(
)
RETURNS DATE
AS
    BEGIN
        DECLARE @CYC_DT DATE

        SELECT
            @CYC_DT = CYC_DT
        FROM
            CYC_DT
        WHERE
            CURR_CYC_IND = 'C'

        RETURN @CYC_DT

END

For a very large table, will these two SQLs perform almost the same?

SELECT
    *
FROM
    [dbo].Table S
    INNER JOIN dbo.CYC_DT D ON  
        S.GL_DT = D.CYC_DT AND
        D.CURR_CYC_IND = 'C'


SELECT
    *
FROM
    [dbo].Table S
WHERE

    S.GL_DT = dbo.GetCycleDate() 

I know avoiding the function is safer, but for testing purposes, I want to be able to rig the function to return a hard coded result without impacting the testing done by other people who are relying on a different date to be set in the Cycle date table. I want to be able to switch to a different date on the without impacting anyone else.

Answer 1

Generally a bad idea to do this, as the optimizer is more limited in what it can do for you when scalar UDFs are involved.:

http://blogs.msdn.com/b/dfurman/archive/2009/12/02/query-performance-scalar-udfs-and-predicate-pushdown.aspx

http://www.sql-server-performance.com/2005/sql-server-udfs/

Since your scalar UDF isn't parameterized, and your sample query is simple, you might get away with it, but you'd have to test to be sure.