2

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?

Faiz
  • 5,331
  • 10
  • 45
  • 57

3 Answers3

7

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • In effect calling getdate in combination with a 'real' select simply bloats the resultset – Steve De Caux Dec 01 '09 at 08:36
  • Can you check this question too [http://stackoverflow.com/questions/1819513/big-performance-difference-1hr-to-1-minute-found-in-sql-can-you-explain-why] – Faiz Dec 01 '09 at 09:04
  • And what can be done if we need the time at which a particular row was fetched? – Faiz Dec 01 '09 at 09:13
  • 1
    You can't get the time a particular row was 'fetched' because that contradict the fundamental relational algebra that powers SQL as a language. You describe the desired result set, the engine returns the result set. Even if you could times from the underlying operations the 'fetch' time of a query is a pretty loose concept, given that a row can suffer several transofrmations during execution, can be spooled, read multiple times, etc etc. – Remus Rusanu Dec 01 '09 at 20:31
  • @Remus: Thanks for the comment. But can you explain why SQL prefers to evaluate parameters of a table valued function for each row processed inside the function (as given in the question in my comment above) ? – Faiz Dec 06 '09 at 09:09
  • 1
    'evaluate for each row processed inside a function' is a procedural statement in which you expect a certain execution path and order. – Remus Rusanu Dec 06 '09 at 17:26
0

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

just somebody
  • 18,602
  • 6
  • 51
  • 60
  • 1
    I tried this: BEGIN TRANSACTION; SELECT CURRENT_TIMESTAMP; EXECUTE LongRunningProc; SELECT CURRENT_TIMESTAMP; COMMIT TRANSACTION; -- and got different results for CURRENT_TIMESTAMP. – onedaywhen Dec 01 '09 at 08:44
  • Here's another post that references the docs which backs up this claim: http://dba.stackexchange.com/a/63549 – odigity Jan 28 '15 at 17:47
  • http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT – odigity Jan 28 '15 at 17:47
  • @odigity well, that's the behavior prescribed by SQL, whereas the question is about SQL Server which is to SQL as bastard is to star. – just somebody Jan 30 '15 at 18:16
  • That had not occurred to us, dude. – odigity Jan 30 '15 at 18:30
0

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

NeuroSys
  • 41
  • 4
  • Then can you answer why [http://stackoverflow.com/questions/1819513/big-performance-difference-1hr-to-1-minute-found-in-sql-can-you-explain-why] is happening? – Faiz Dec 01 '09 at 09:12