SQL - @@TIMETICKS Function

---

---

The SQL @@TIMETICKS statistical function is used to retrieve the number of microseconds. It returns the total number of microseconds per tick. The SQL server only stores time to approximately 1/300th of a second, whereas a single tick represents one hundred nanoseconds.

Note − In SQL, a Tick is an arbitrary unit for measuring internal system time. How many milliseconds a tick represents depends on the OS(operating system). For the windows OS, there are 10000 ticks in a millisecond.

Syntax

Following is the syntax of the SQL @@TIMETICKS function −

@@TIMETICKS

Return type

The return type of this function is an INTEGER.

Parameters

• It does not accept any parameters.

Return value

This function returns the number of microseconds per tick.

Example

In the following example,we are using the SQL @@TIMETICKS function to retrieve the number of microseconds per tick.

SELECT @@TIMETICKS AS Time_in_microseconds;

Output

The above program produces the following output −

+----------------------+
| Time_in_microseconds |
+----------------------+
| 31250                |
+----------------------+

Example

The following is another example of the SQL @@TIMETICKS function. You can also use the @@CPU_BUSY function along with this function to retrieve the total amount of time in microseconds per tick that the SQL server has spent in active operations.

SELECT @@CPU_BUSY AS Total_spent_time, @@CPU_BUSY * CAST(@@TIMETICKS AS FLOAT) AS Time_in_microseconds;

Output

On executing the above program, it will produce the following output −

+------------------+----------------------+
| Total_spent_time | Time_in_microseconds |
+------------------+----------------------+
| 107695           | 3365468750           |
+------------------+----------------------+

Example

In this example, we are using the SQL @@TIMETICKS and @@IO_BUSY functions along with the GETDATE() function to retrieve the total spent time on input and output operations in microseconds per tick until today.

SELECT GETDATE() AS Todays_date, @@IO_BUSY AS Total_spent_time, @@CPU_BUSY * CAST(@@TIMETICKS AS FLOAT) AS Time_in_microseconds;

Output

Following is the output of the above query −

+-------------------------+------------------+----------------------+
| Todays_date             | Total_spent_time | Time_in_microseconds |
+-------------------------+------------------+----------------------+
| 2023-03-01 16:58:16.213 | 52253            | 3367937500           |
+-------------------------+------------------+----------------------+