getdate() in an update query vs. datetime variable

• GabyYYZ

  SSCertifiable

  Points: 7913

  More actions

  July 9, 2009 at 7:37 am

  #131881

  Hi folks,

  Periodically I get a request from the business folks to update a date column to today's date for a whole bunch of articles. After loading those articles into a temp table, I generate a query along the lines of:

  update target_table

  set DateChanged = getdate()

  where articleNumber in (select articleNumber from #temp_Articles)

  Is getdate() guaranteed to be the same for all the changes, as this is occurring in one batch or would it be better to assign getdate() to a variable first then replace the update with set DateChanged = @today?

  Thanks.

  Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

• bcronce

  SSCrazy

  Points: 2597

  More actions

  July 9, 2009 at 8:43 am

  #1022426

  I *think* it's called for each row

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  July 9, 2009 at 8:48 am

  #1022434

  getdate() will return a consistant value within a statement. it doesent matter how long it take for the statement to execute.

  edit : See here for more info http://sqlblog.com/blogs/andrew_kelly/archive/2008/03/01/when-a-function-is-indeed-a-constant.aspx

  ---

  
  Clear Sky SQL
  My Blog[/url]

• GabyYYZ

  SSCertifiable

  Points: 7913

  More actions

  July 9, 2009 at 1:40 pm

  #1022641

  Dave Ballantyne (7/9/2009)

  ---

  getdate() will return a consistant value within a statement. it doesent matter how long it take for the statement to execute.

  edit : See here for more info http://sqlblog.com/blogs/andrew_kelly/archive/2008/03/01/when-a-function-is-indeed-a-constant.aspx

  Hmmm...I'll simulate this on a large fragmented table, no indexes, to insure enough lag. Seems the blog also had a difference of opinion, unresolved. For now, to play it safe, I'll use a variable, just to be sure, until I can verify this. Will let everyone know once done.

  Thanks.

  Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

• GabyYYZ

  SSCertifiable

  Points: 7913

  More actions

  July 9, 2009 at 1:52 pm

  #1022646

  Gaby Abed (7/9/2009)

  ---

  Dave Ballantyne (7/9/2009)

  ---

  getdate() will return a consistant value within a statement. it doesent matter how long it take for the statement to execute.

  edit : See here for more info http://sqlblog.com/blogs/andrew_kelly/archive/2008/03/01/when-a-function-is-indeed-a-constant.aspx

  Hmmm...I'll simulate this on a large fragmented table, no indexes, to insure enough lag. Seems the blog also had a difference of opinion, unresolved. For now, to play it safe, I'll use a variable, just to be sure, until I can verify this. Will let everyone know once done.

  Thanks.

  Okay, definitely confirmed that getdate() in a statement is the same, regardless of how long the query takes. I created a very basic table of a dummy data column and a datetime default getdate() column. In inserted into the table from itself 21 times (doubled each loop) to get over 1,000,000 rows, and did the update. A couple seconds passed that should have registered the difference.

  Thanks all.

  Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein