SQL Server has a less known datatype called “TimeStamp”. But I wasn't able to find any article about it on the web. So I thought I would try a sample myself and write about it.
TimeStamp is actually used for record versioning in a table. When you Insert (or) Update a record in a table with a TimeStamp field, that field gets updated automatically.
Lets create a sample table to understand the usage of TimeStamp field.
Create table TimeStampExample
(
RunningNumber int identity,
LastName varchar(30),
tStamp timestamp
)
The above table “TimeStampExample” has been created with a TimeStamp field (tStamp). By the way its not mandatory to provide a field name for timestamp columns. The below table structure is perfectly valid only.
Create table TimeStampExample
(
RunningNumber int identity,
LastName varchar(30),
timestamp -- note we haven't mentioned a field name here
)
When a record is inserted the value of tStamp gets automatically set by SQL Server. Lets see that in action.
Insert TimeStampExample (LastName) values ('RajiniKanth')
After inserting a sample record lets query the table to see the complete record with timestamp value in it.
Select * from TimeStampExample
We need to understand that TimeStamp are just binary values that are incremented with every change in the database and therefore they are unique with the database.
For better understanding lets update the record and check whether tStamp has been updated or not. Make use of the below code snippet for checking that.
Update TimeStampExample Set LastName = 'Shivaji Rao' Where LastName = 'RajiniKanth'
Just query the table now and you could see that the TimeStamp field being updated automatically. Cool isn't it :)
TimeStamp is actually used for record versioning in a table. When you Insert (or) Update a record in a table with a TimeStamp field, that field gets updated automatically.
Lets create a sample table to understand the usage of TimeStamp field.
Create table TimeStampExample
(
RunningNumber int identity,
LastName varchar(30),
tStamp timestamp
)
The above table “TimeStampExample” has been created with a TimeStamp field (tStamp). By the way its not mandatory to provide a field name for timestamp columns. The below table structure is perfectly valid only.
Create table TimeStampExample
(
RunningNumber int identity,
LastName varchar(30),
timestamp -- note we haven't mentioned a field name here
)
When a record is inserted the value of tStamp gets automatically set by SQL Server. Lets see that in action.
Insert TimeStampExample (LastName) values ('RajiniKanth')
After inserting a sample record lets query the table to see the complete record with timestamp value in it.
Select * from TimeStampExample
We need to understand that TimeStamp are just binary values that are incremented with every change in the database and therefore they are unique with the database.
For better understanding lets update the record and check whether tStamp has been updated or not. Make use of the below code snippet for checking that.
Update TimeStampExample Set LastName = 'Shivaji Rao' Where LastName = 'RajiniKanth'
Just query the table now and you could see that the TimeStamp field being updated automatically. Cool isn't it :)
Comments
values ( needs single quote on both sides)
you meant note when you wrote not
Thanks for the info
But things arent that simple and cool with this data type. Microsoft itself suggests not to use this Data Type anymore as there are several problems with it, for example:
1. You cant query on what date and time a particular row was updated (with the help of timestamp I mean)
2. In order to query on Timestamp, you got to know and store the DBTS Values somewhere so that you can use those Binary Values in your query. This can be done by querying SELECT @@DBTS, GETDATE()
3. The other major problem with Timestamp is that it generates a guaranteed Unique Value in one Database on one Server. If you restore the Database on a different Server, the DBTS Values are reset and that might upset your whole system.
4. In order to prevent the above from happening, you got to Restore the MODEL database first on a new server and then your database. But in some cases that too doesnt work properly.
5. Also note that the only reason Microsoft still keeps this Datatype is the backward compatibility. This type came to SQL Server when they bought it from Sybase 4.2. They had to keep this Data Type as they wanted to support the existing people who are already using it in their systems.
5. Currently, SQL Server only uses this data type for Recovery operations.
Have you ever noticed all these problems ?
Nariman Ghanghro
I utilize the timestamp column in a third-party vendor source system to identify the delta (what has changed since my last extraction.) And while this isn't the first club I'd reach for usually, given that the vendor includes and exposes the timestamp column in most major tables (and marks rows as retired in lieu of row deletions) but doesn't provide a vendor-blessed API or way to identify the delta, I've found it useful.
1. You cant query on what date and time a particular row was updated (with the help of timestamp I mean)
Once I've identified the delta in the source system, I utilize my own controlled (and frozen) SequenceId
in the downstream warehouse.
2. In order to query on Timestamp, you got to know and store the DBTS Values somewhere so that you can use those Binary Values in your query. This can be done by querying SELECT @@DBTS, GETDATE()
Absolutely. The timestamp value varies in the source system. I convert it's value to a varbinary(8) to freeze it in the warehouse. With the knowledge of the the frozen varbinary(8)'s in the warehouse, I can ask "What's new or modified?" in the source system.
3. The other major problem with Timestamp is that it generates a guaranteed Unique Value in one Database on one Server. If you restore the Database on a different Server, the DBTS Values are reset and that might upset your whole system.
So there may be a discontinuity in the robustly monotonically increasing timestamp values upon a restoration event. So, as mentioned above, I work in my own frozen varbinary(8)'s, and I provide a varbinary(8) source adjustment in case a server switch makes it necessary.
I can't wait for SQL 2008's data change capture capability to make all of this unnecessary.