getdate() default vs. getdate() in query

• ffoerster

  Right there with Babe

  Points: 796

  More actions

  October 13, 2015 at 3:50 pm

  #305504

  Hi, a particular case in our production db made me wonder today.

  -- we have a table

  CREATE TABLE dbo.SamplePrc ( Instrument int, TmIndex tinyint, Prc float, GenDt datetime DEFAULT (GETDATE()) )

  -- and a table

  CREATE TABLE dbo.BufferSamplePrc ( Instrument int, TmIndex tinyint, Prc float, GenDt datetime not null )

  -- we then use 1 procedure to insert into both tables, not in a single transaction though

  CREATE PROC dbo.InsertSamplePrc

  @instrument int, @tmIndex int, @prc, float

  AS BEGIN

  INSERT dbo.SamplePrc (Instrument, TmIndex, Prc) VALUES (@instrument, @tmIndex, @prc);

  INSERT dbo.BufferSamplePrc (Instrument, TmIndex, Prc, GenDt) VALUES (@instrument, @tmIndex, @prc, GETDATE());

  END;

  We end up with different times tamps (by seconds) for the two tables, especially when a program calls this procedure multiple times in parallel.

  3 calls (picked from XE)

  #1, RPC:started @ 12:30:05.090, RPC:Completed @ 12:30:11.720, SamplePrc.GenDt @ 12:30:05.090, BufferSamplePrc.GenDt @ 12:30.07.445

  #2, RPC:started @ 12:30:05.097, RPC:Completed @ 12:30:11.723, SamplePrc.GenDt @ 12:30:11.723, BufferSamplePrc.GenDt @ 12:30.11.723

  #3, RPC:started @ 12:30:05.260, RPC:Completed @ 12:30:11.727, SamplePrc.GenDt @ 12:30:11.727, BufferSamplePrc.GenDt @ 12:30.11.729

  What I am wondering is when the value for the GETDATE() default gets allocated vs. when the value for the explicit GETDATE() in the inserts gets allocated.

  Is the default constraint value subject to getting the lock on the table?

  Anyone has an idea?

  Thanks in advance.