requirement is, both field must be equal, what would you do
declare @var datetime
set @var = getdate()
update table set f1=@var,f2=@var
or simply
update table set f1=getdate(),f2=getdate()
views:
21answers:
4
+4
A:
Definitely the first way, because 2 calls to getdate() will most likely return different values.
dcp
2010-07-28 13:42:40
+1 I think you're right it can return different results.
Martin Smith
2010-07-28 13:55:46
@Martin Smith, did you see it happening?
Fredou
2010-07-28 13:56:59
@Fredou - If it were me, I would still use the first way even if using the second way doesn't cause a problem. There's nothing to stop Microsoft from changing the way this behavior works in future versions of SQL Server, so if you are relying on it then your code will break. In my opinion, it's always best to do things in the clearest manner you can. With the first way, nobody has to look at the code and wonder, "hmmm, is this really doing what it should"?
dcp
2010-07-28 14:01:44
@Fredou - No I didn't see it happening as I guess you'd have to run **a lot** of tests to get this to occur but the compute scalar operator in the execution plan seemed to be calling it twice so I think it evaluates it separately for each column but not for each row and this should be the accepted answer :-)
Martin Smith
2010-07-28 14:04:50
@dcp, that behavior changed in SQL2005. Now it is a non-deterministic function and may return different values every time you call it.
Raj More
2010-07-28 14:04:59
@Raj More - Thanks very much, you just made my point :).
dcp
2010-07-28 14:06:45
@Raj - GetDate() can never have been deterministic under this definition of deterministic http://msdn.microsoft.com/en-us/library/aa214775%28SQL.80%29.aspx as it depends on the current time!
Martin Smith
2010-07-28 14:07:35
A:
Actually, this depends on the version of SQL.
GetDate() was a deterministic function prior to SQL 2005. The answer returned was the same value for the duration of the statement.
In SQL 2005 (and onwards), Getdate() is non-deterministic, which means every time you call it you will get a different value.
Since both GetDate() functions will be evaluated before the update starts, IMO they will come back with the same value.
Not knowing the size of your table and partitions and the load on your server, I would go with option #1
Raj More
2010-07-28 13:43:32
I ran my 'select' test in my answer in SQL2008. I think deterministic just means repeated invocations with the same parameters give the same result.
Martin Smith
2010-07-28 13:48:00
+2
A:
Original Answer: getdate() seems to be like rand() and only evaluated once in a query. This query took more than a minute to return and all getdate()s are the same.
select getdate()
from sys.objects s1, sys.objects s2, sys.objects s3
Updated But when I looked at the query plan for an update of 2 different columns I could see the compute scalar operator was calling getdate() twice.
I tested doing an update with rand()
CREATE TABLE #t(
[f1] [float] NULL,
[f2] [float] NULL,
)
insert into #t values (1,1)
insert into #t values (2,2)
insert into #t values (3,3)
update #t set f1=rand(),f2=rand()
select * from #t
That Gives
f1 f2
---------------------- ----------------------
0.54168308978257 0.574235819564939
0.54168308978257 0.574235819564939
0.54168308978257 0.574235819564939
Martin Smith
2010-07-28 13:44:19
interesting way of trying it, let me try :-) and after 2,000,000 rows, the value was the same for all of them
Fredou
2010-07-28 13:45:33
@Fredou - I think dcp's answer is correct though I'll update mine in a second.
Martin Smith
2010-07-28 13:56:24
A:
I'm going to go with something other than performance: readability / communication of intent.
Along those lines, option one is probably better. You are, in effect, telling future developers "I am explicitly setting f1 and f2 to the same DateTime." If the requirements change in the future, and (for some reason) f1 and f2 have to be updated at separate times (or something changes and they get evaluated at different times), you still have the same datetime for both.
In option two, all you're saying is that f1 and f2 have to be updated with the current time of whenever their update operations run. Again, if something changes in your requirements and they have to be evaluated in separate statements for some reason, now they won't necessarilly be the same value any more.
AllenG
2010-07-28 13:55:18