Identity field and datetime default with get date earlier

Question

Fellow DBAs,

1 - I have run into a "randomly occuring" anomaly from a vendor's sp.

The sp runs a fetch cursor. it reads a driver table that has dynamicic sql in one of the fields.

it executes the dynamic sql inside the fetch cursor loop - row by row.

2 - The dynamic sql is doing an insert from a driver table. Similar to below:

Insert into DataTransfer (myfield1, myfield2)

Select myfield1, myfield2 from masterData

where id > @myparam

Begin Commit

-- @sqlquery holds the statement example above

exec (@sqlquery)

Commit Transaction

3 - Mykey is the identity field on the DataTransfer tabe. Creation_Time field on the DataTransfer table has a default using getdate(). THERE ARE MORE THAN ONE RECORD COMING OUT OF THE SELECT FOR THE INSERT!!!

The issue is that on rare occasions, we see results as follows:

 

mykey                  Creation_Time

58141140            7/13/2023  4:01:00 PM

...

...

...

58155503        7/13/2023  3:56:01 PM

What would cause a higher identity field value with a timestamp at an earlier date?

The sp is ONLY RUN ONCE at a time.

Thanks,
MG

Answer 1

It is not unheard of time seemingly going backwards in modern computers. The timestamps may be collected on different processors which due to balanced power plans and alike are running on different clock speeds.

However, you usually do not see time falling back more than a few milliseconds, and in this example it appears to be minutes. So I suspect that there is something in the code that you don't show us which is the reason. Just because a column has a default does not mean that this is the actual value. The code may still set an explicit value on INSERT or update it later.