SYSDATETIME() returns same precision as GETDATE() in Azure SQL

Question

When calling the SYSDATETIME() function I expect it to return a DATETIME2 value with full seven-decimal precision. However, I discovered that on an Azure SQL instance the return value from SYSDATETIME() is a DATETIME value with 3/100 second precision, (no, a DATETIME does not have full 3-decimal precision).

Obviously, if you rely on the full 7-digit precision in your logic, this is a pretty serious matter. Surely, this can not be an intended behavior?

To replicate, run the script below on an on-prem and Azure SQL instance:

drop table if exists mytable
create table mytable (dt datetime2)
go

insert mytable
select sysdatetime()
go 1000

-- Group and count the last five digits
select right(cast(dt as varchar(28)), 5) as thousands, count(*) as [count]
from mytable
group by right(cast(dt as varchar(28)), 5)

Result when run on an Azure SQL instance:

thousands count

---

00000 381
33333 314
66666 305

When the exact same script is run on an on-prem instance, the result is more what I had expected:

thousands count

---

00053 2
00092 1
00487 2
00614 1
00645 1
00722 1
00724 1
00832 1
01414 2
01537 2
01590 1
...
98898 1
99893 1
99979 1

Edited for brevity. The on-prem script returned 784 unique rows, which is an expected distribution.

Answer 1

Try using SYSUTCDATETIME() instead when using Azure SQL Database. For example,

SELECT (SYSUTCDATETIME() AT TIME ZONE 'UTC')

Answer 2

I ran this in my Azure SQL DB:

   drop table if exists mytable  
   create table mytable (dt datetime2, dtutc datetime2)  
   go  
     
   SET NOCOUNT ON  
   declare @i int = 10000  
   WHILE @i > 0  
   BEGIN  
      insert mytable  
      select sysdatetime(), sysutcdatetime()  
      SET @i -=1   
   END  
     
   SELECT dt, COUNT(*)  
   FROM   mytable   
   GROUP  BY dt  
   ORDER BY dt  
     
   SELECT dtutc, COUNT(*)  
   FROM   mytable   
   GROUP  BY dtutc  
   ORDER BY dtutc  

Here is part of the output:

What we can see it that there is much as up to 10 ms between each INSERT, so this proves nothing with regards to sysdatetime().

Well, we can fix that: wrap the loop in a transaction:

   BEGIN TRANSACTION  
   SET NOCOUNT ON  
   declare @i int = 10000  
   WHILE @i > 0  
   BEGIN  
      insert mytable  
      select sysdatetime(), sysutcdatetime()  
      SET @i -=1   
   END  
   COMMIT  TRANSACTION  

Now I get this output:

That makes it crystal clear: sysdatetime() on Azure SQL DB has the same precision as getdate(). But at least it is better than sysutcdatetime(), which only gives you 16 ms!

When running in the box product, sysdatetime() typically produces value for every millisecond, so you could say that it has a resolution of 1 ms. However, time in modern computers is a difficult thing. If you caputure sysdatetime() in rapid succession you may find that time may seem to be going backwards! I think this is because different processors runs at different speed and are not in perfect sync.

In any case, you only have use for the full scale of 7 fractions of datetime2, if you are getting data from some high-resolution clock outside SQL Server.