sql server 2005 - Selecting GETDATE() function twice in a select list-- same value for both?

Question

I have a SELECT statement that uses GETDATE() for two different column values. I'm wondering if by the nature of things those two separate function calls in the same SELECT will return identical values every time?

Answer 1

No they aren't guaranteed to return identical values every time. Each individual reference to GetDate() is a runtime constant and will keep its value throughout the query...

SELECT GETDATE()
FROM large_table

will return the same value in all rows regardless of how long the query takes to run.

But there is no guarantee that different references will have the same value.

You can see this as below

SET NOCOUNT ON;

DECLARE @T TABLE 
(
rownum INT IDENTITY(1,1) PRIMARY KEY,
d1 DATETIME,
d2 DATETIME
)

WHILE (5 > (SELECT COUNT(*) FROM @T WHERE d1 <> d2))
    BEGIN
    DELETE FROM @T WHERE d1 = d2
    INSERT INTO @T 
    SELECT GETDATE(),GETDATE()
    END

SELECT * FROM @T

Example Results

rownum      d1                      d2
----------- ----------------------- -----------------------
22381       2011-05-18 12:24:14.433 2011-05-18 12:24:14.437
30912       2011-05-18 12:24:15.420 2011-05-18 12:24:15.423
43234       2011-05-18 12:24:16.717 2011-05-18 12:24:16.720
113360      2011-05-18 12:24:24.210 2011-05-18 12:24:24.213
147855      2011-05-18 12:24:27.817 2011-05-18 12:24:27.820