Does SQL repeat a function for each row or only 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 SQL servers work 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 that avoiding using a function is safer, but for testing purposes, I want it to be able to execute the function in order to return a hard coded result without affecting the testing performed by other people who rely on a different date to be set to the table cycle dates. I want to be able to switch to another date without affecting anyone else.

Answer 1

This is usually a bad idea, 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/

UDF , , , .