How to verify the precession of the server system time in SQL Server Transact-SQL?

You can run a WHILE loop to check the precision of server system time in Transact-SQL as shown below:

DECLARE @var DATETIME2(7);
DECLARE @count INT = 0;
WHILE @count<100 BEGIN
	SET @count = @count + 1;
	SET @var = SYSDATETIME();
	PRINT CAST(@count AS CHAR(3))+': '
	   +CAST(CAST(@var AS TIME(7)) AS CHAR(16));
END
---------------------
1  : 19:39:22.3348095
2  : 19:39:22.3348095
...
13 : 19:39:22.3348095

14 : 19:39:22.3358096
15 : 19:39:22.3358096
...
78 : 19:39:22.3358096

79 : 19:39:22.3968131
80 : 19:39:22.3968131
...
100: 19:39:22.3968131

As you can see from the output, the actual precision of the SYSDATETIME() function is very poor, about 0.001 second. This is much lower than 0.0000001 second of what SYSDATETIME() can return.

There are 3 main factors affecting the actual precision of the SYSDATETIME() function:

• The precision of the time function of the server operating system.
• The multi-tasking behavior of the server operating system, since the SQL Server task needs to be put on sleep frequently by the operating system.
• The multi-session behavior of the SQL Server, since your client session task needs to be put on sleep frequently by the SQL server task.

 

⇒Date/Time Operations and Functions in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials