sql server with an update and 2 datetime field and getdate()

requirement is, both fi开发者_开发技巧eld 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()

---

Definitely the first way, because 2 calls to getdate() will most likely return different values.

---

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

---

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

---

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.