Considering below code,what do you think the output will be …?Do you think new value of getdate() and Rand() will be calculated for every row..?
create table test_ct ( N int, datee datetime, newidd varchar(255) default newid() ) insert into test_ct (N,Datee) select N,getdate() from numbers where n<10
Before diving into the answer,let me recap few things.TSQL functions can be Deterministic or non Deterministic.See here for more info..
In the above code getdate() is calculated only once and newid is calculated for every row..
Below is the output:
There are many functions which exhibhits the same behaviour.The only way to see how the function is calculated is by looking at its execution plan and look for Constexpression
<OutputList> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[numbers]" Column="N" /> <ColumnReference Column="ConstExpr1009"> <ScalarOperator ScalarString="getdate()"> <Intrinsic FunctionName="getdate" /> </ScalarOperator> </ColumnReference> <ColumnReference Column="Expr1008" /> </OutputList>
That’s pretty much about constant expressions..
Resources and additional References:
http://www.sqlskills.com/blogs/conor/rand-and-other-runtime-constant-functions-redux/ —must read the comments