How to force generation of a new sysdatetime()?

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

Answer

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.