Jun 062016
 
 Posted on: June 6, 2016  Posted by  Add comments

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:

Screenshot DBTS timestamp sqlchamp                  Screenshot DBTS timestamp sqlchamp

 

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.

 

MIN_ACTIVE_ROWVERSION2 timestamp

 

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

Motivate me by giving compliments\comments

error: Content is protected !!