0

I have a webApi which registers orders, using a stored procedure, assigning the creation date = SYSDATETIME(). Every request registers one unique order.

The problem is than two different requests to my Api, in the same second, separated less than 200 milliseconds, that generate two different calls to my stored procedure (practically separated by a few milliseconds), are registered with the same date, exactly the same, with precission of milliseconds.

for example:

  1. order 1: 100001 -> creation date = 2020-12-01 01:01:01.1234567
  2. order 2: 100002 -> creation date = 2020-12-01 01:01:01.1234567

This is the code in my stored procedure

declare @date datetime2
select @date = SYSDATETIME()`

Theorically the sql function SYSDATETIME() doesn't repeat values, but in my case, they are repeated (several times in my database in different dates).

Any idea of what's happening?

Thom A
  • 88,727
  • 11
  • 45
  • 75
Juanma R.
  • 281
  • 1
  • 3
  • 7
  • "Theoretically the sql function SYSDATETIME() doesn't repeat values" - where did you get that idea from? It's job is to report the current time (to whatever appropriate accuracy it does), not to produce unique values. – Damien_The_Unbeliever Jan 14 '21 at 11:04
  • The Windows system clock as exposed by SQL Server's functions typically has accuracy no better than 15 ms (though this can vary, and is not under the control of SQL Server). Add to this the fact that `DATETIME` itself has precision only to 3 ms ([more or less](https://learn.microsoft.com/sql/t-sql/data-types/datetime-transact-sql#rounding-of-datetime-fractional-second-precision)) and it's clear why you generally shouldn't rely on timestamps for uniqueness. – Jeroen Mostert Jan 14 '21 at 11:13
  • 1
    SQL Server has the capacity to process many request simultaneously. If you're effecting multiple rows at the same time, then it's very easy to get the same date and time, even to the nearest 1/300th or even 1/1000000th of a second. If you're, for example, inserting multiple rows at the same time and using a function like `SYSDATETIME` then it's almost guaranteed that many of the rows will share the same value as many other rows in the set. If you generate 1,000 rows at the same time (trivial for SQL Server), they'll all very liekly have the same value for `SYSDATETIME`. – Thom A Jan 14 '21 at 11:31

1 Answers1

1

From the docs:

Note

SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.

So there can certainly be doubled up data. Windows does have a high precision clock, but SQL Server doesn't use it. If you need it, CLR might be an option.

Charlieface
  • 52,284
  • 6
  • 19
  • 43