Timestamp and @@DBTS

@@DBTS function returns the current timestamp value of  a database.

Timestamp is a data type that generates a unique timestamp binary value for the database. This is an auto incremental value that increments only when we insert or update a row in a table. A table can have only one timestamp column. Each database has its own auto incremental counter for the timestamp value. So, It will always generate a unique value with in a database.

we should not use this type column for key columns. Because, the value of the column will change automatically. So, we will lose the reference. We can use this for row version. We can also use this timestamp column to check whether the row is updated or not.

Implementation

This is an example for @@DBTS function.

use AdventureWorks2012
select @@DBTS

Now, I am going to create a new table with timestamp column and I will insert few rows in it.

Create table tblTimeStamp
(
RowNo smallint,
RowTimeStamp timestamp
)

Select @@DBTS
insert into tblTimeStamp (RowNo) values(1)
GO
Select @@DBTS
insert into tblTimeStamp (RowNo) values(2)
GO
Select @@DBTS

Output:

Now, You can update these rows in your machine and get the latest timestamp value.

We have learned about timestamp data type and @@DBTS function in this blog post. I will talk about rowversion in the next blog post. rowversion is the synonym for the timestamp data type.

 

About these ads