How to force generation of a new sysdatetime()?

Question

I tried to create a function to generate bigint UID based on the timestamp from this solution. While accuracy should be sufficient to avoid collisions, the server obviously does not measure the datetime2 returned by sysdatetime() each time it is called. In a loop, I am able to generate dozens of identical UIDs before a change occurs. I'm not sure exactly how it works inside, but is there any way to force the generation of a new datetime2 with each call?

create function [func].GenerateBigIntID()
returns bigint
as
begin
  declare @Binary binary(9) = cast(reverse(cast(sysdatetime() as binary(9))) as binary(9)) 
  return(select cast(substring(@Binary, 1, 3) as bigint) * 864000000000 + cast(substring(@Binary, 4, 5) as bigint))
end

If the loop is tight enough, `sysdatetime()` will return the same value in more than one iteration since not enough time will pass between the two. Nothing you can do about that. Why not simply use a [sequence](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017)? — Zohar Peled, May 20 '19 at 10:27
Each time you call `sysdatetime` the time is evaluated at that point. If you have the query `SELECT sysdatetime(),sysdatetime(),sysdatetime(),sysdatetime(),sysdatetime(),` then it'll evaluate it 5 times; just that all 5 values will be the same as they were all evaluated at the same time. This sounds like an XY Problem. Also, you say you're using a loop; why? SQL Server excels at dataset methods, not iterative ones. — Thom A, May 20 '19 at 10:27
@Larnu Normally, I have consecutive commands that each use this function, and sometimes I get errors about the duplicate key, so I tried to run the function in a loop just for the test to see how often it would change. — Triber, May 20 '19 at 10:30
You still didn't explain why you want that id based on sysdatetime. SQL Server provides you with two out-of-the-box, very easy ways to auto-generate a unique number (well, unique as long as you don't mess with it yourself), why not take advantage of that? — Zohar Peled, May 20 '19 at 10:43
@ZoharPeled I will probably use some of what others are suggesting, although it will mean more adjustments than I would like, but it does not change the fact that I am still wondering if it could be done. — Triber, May 20 '19 at 10:48
T-SQL is partially dependent on the resolution of the clock as provided by the OS here as well (typically around 15 ms), in addition to any concerns about the value being cached/unified with other instances by the optimizer, and there is no T-SQL function to access the high-resolution timestamp (even assuming it's available). Empirically, a `WAITFOR DELAY '00:00:00.01'` is "long enough" to force an increment, but of course this behavior is far from guaranteed. Use an identity column, `NEWSEQUENTIALID()`, a `SEQUENCE` or combine with `CRYPT_GEN_RANDOM` (for "mostly sequential" values). — Jeroen Mostert, May 20 '19 at 10:56

score 1 · Accepted Answer · answered May 20 '19 at 11:03

Short version: No, that's not possible. If the loop is tight enough, SysDateTime will return the same value for more than one iteration of the loop.

Long version:

SysDateTime() returns the current date and time as DateTime2(7).
It gets the current date and time values by using GetSystemTimeAsFileTime() win-api function.
This means that even though it returns a DateTime2 with 100 nano-seconds accuracy, The actual accuracy of the value returned depends on the computer hardware and version of Windows on which the instance of SQL Server is running.

I'm guessing that on a computer that can get very accurate results from that win-api function, and yet do slow loops in SQL, you might be able to get unique ids in a loop - However, I couldn't even guess what kind of hardware, windows version, and general settings you would need to get that (or if it even possible).

Starting with 2012 version, the best way to generate a unique bigint value not depending on an insert to a table is to use a sequence.

How to force generation of a new sysdatetime()?

1 Answers1