Getdate() returning same value at different times! What is happening?

Question

The following SQL run against a table with 1 million records is giving the same value for columns Date1 and Date2, and it took 38 seconds to execute. Is this an expected behavior and why?

CREATE FUNCTION Fn_Test(@a decimal)RETURNS TABLE
AS
RETURN
(
    SELECT @a Parameter, Getdate() Date1, PartitionTest.*
    FROM PartitionTest
);

SELECT *, GETDATE() Date2 FROM Fn_Test(RAND(DATEPART(s,GETDATE())))

Is this some kind of caching?

Answer 1

Yes. SQL semantics do no require that a non-deterministic function that does not depend on the current row to be evaluated for each row. It is allowed to evaluate the function once and return the same value for all subsequent rows.

Answer 2

GETDATE() (and CURRENT_TIMESTAMP) return the timestamp of the start of the transaction

Answer 3

SqlServer is performing a the GETDATE() at first and then replaces the value trough all the rows.