Home > T-SQL > Timestamp and @@DBTS

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

pic1Now, 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:

pic2Now, 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.

 

Advertisements
  1. No comments yet.
  1. January 31, 2013 at 11:59 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: