Skip to main content

About TimeStamp datatype of SQL Server ...

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 :)

Comments

byerspc said…
You have a couple typos

values ( needs single quote on both sides)

you meant note when you wrote not

Thanks for the info
Vadivel said…
Byerspec, Thanks for pointing out the Typo, I have corrected it now.
Anonymous said…
Nice try mate,

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
George Jetson said…
Where's the love for my boy the timestamp column? ;)

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.
Gabbar said…
Nice info Vadivel and George.

Popular posts from this blog

Registry manipulation from SQL

Registry Manupulation from SQL Server is pretty easy. There are 4 extended stored procedure in SQL Server 2000 for the purpose of manupulating the server registry. They are: 1) xp_regwrite 2) xp_regread 3) xp_regdeletekey 4) xp_regdeletevalue Let us see each one of them in detail! About xp_regwrite This extended stored procedure helps us to create data item in the (server’s) registry and we could also create a new key. Usage: We must specify the root key with the @rootkey parameter and an individual key with the @key parameter. Please note that if the key doesn’t exist (without any warnnig) it would be created in the registry. The @value_name parameter designates the data item and the @type the type of the data item. Valid data item types include REG_SZ and REG_DWORD . The last parameter is the @value parameter, which assigns a value to the data item. Let us now see an example which would add a new key called " TestKey ", and a new data item under it called TestKeyValue :

Screen scraping using XmlHttp and Vbscript ...

I wrote a small program for screen scraping any sites using XmlHttp object and VBScript. I know I haven't done any rocket science :) still I thought of sharing the code with you all. XmlHttp -- E x tensible M arkup L anguage H ypertext T ransfer P rotocol An advantage is that - the XmlHttp object queries the server and retrieve the latest information without reloading the page. Source code: < html > < head > < script language ="vbscript"> Dim objXmlHttp Set objXmlHttp = CreateObject("Msxml2.XMLHttp") Function ScreenScrapping() URL == "UR site URL comes here" objXmlHttp.Open "POST", url, False objXmlHttp.onreadystatechange = getref("HandleStateChange") objXmlHttp.Send End Function Function HandleStateChange() If (ObjXmlHttp.readyState = 4) Then msgbox "Screenscrapping completed .." divShowContent.innerHtml = objXmlHttp.responseText End If End Function </ script > < head > < body > &l

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul