How to get last used timestamp in SQL Azure database?

A timestamp is the time at which an event is recorded (inserted or updated), and timestamp is guaranteed to be unique in the database. So what will you do if for some reason you need to find the last used timestamp in SQL Azure Database? Well, the answer is @@DBTS function.

@@DBTS

This function is there since SQL Server 2000 and also available in SQL Azure Databases. The function returns the value of the current timestamp data type in the current database.

Here is an example which shows how the result looks like:

                 

 

This function is applicable on all versions starting SQL Server 2000 till 2016 Including SQL Azure Databases.

MIN_ACTIVE_ROWVERSION

Just like this there is another function MIN_ACTIVE_ROWVERSION

The rowversion data type is also known as timestamp.

This function returns the lowest active rowversion value in the current database. A rowversion value is active if it is used in a transaction that has not yet been committed.

Just like timestamp value a new rowversion gets generated when an insert or update is performed on a table with rowversion column. If there are no active values in the database, MIN_ACTIVE_ROWVERSION returns the same value as @@DBTS + 1.

 

 

what’s the main difference between both these options?

Since Min_Active_RowVersion takes care of the open transaction as well – it increments the value of MIN_ACTIVE_ROWVERSION even if the transaction(insert\update) fails\rollbacks & you can see the incremented value only after the transaction completes, but @@DBTS increments the value just after the transaction begins, it doesn’t wait for the transaction to complete. which helps to get last used timestamp for the given database.
Hope you liked the post.

Happy Learning!

feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLChamp