@@DBTS correct usage

Hi,

Suppose my Test table has a timestamp (rowversion) column, and I have the following code:

INSERT INTO Test ...

SELECT @.@.DBTS as 'TS'

Can I be sure the TS return column value contains the value of the timestamp column of the Test table? I suspect no in general (although it will work "most of the time"), but I would like a confirmation.

If I am correct, and there may be a mismatch, is there any clever way of getting the exact value, except from doing a SELECT WHERE ...?

Thanks.

Can I be sure the TS return column value contains the value of the timestamp column of the Test table?

If the BOL said "@.@.DBTS Returns the value of the current timestamp data type for the current database", then you couldn't.

Anyway, there are some other ways to find the records have been inserted.

|||

INSERT INTO Test ...

SELECT @.@.DBTS as 'TS'

You are correct, this will not work in a lot of cases since the timestamp (better to start using the rowversion synonym too) value is shared amongst all tables. It would require a database lock to prevent this from possibly failing

Doing a SELECT is the best way, and will almost always be really fast since:

1. You will likely have the primary key value
2. The row will be in cache since you just created it.

@@DBTS

Hi

There are tables in our database with timestamp column. As and when rows are updated/inserted in these tables, the timestamp column is getting populated which is getting reflected in the @.@.DBTS system variable.

However when we take the FULL database backup and restore the database, the @.@.BSTS is not showing the same timestamp value as it was when full DB backup was done (the behavior is unpredictable - once i did manage to get the correct @.@.DBTS value).

Does backup and restore process have any effects on the @.@.DBTS system variable.

Any guidance on parameter settings for Backup and restore will be appreciated to resolve this issue.

Cheers

Nishant Hate

I am assuming that is because Timestamp is a binary datatype used for row versioning, if you want the timeback you need to use datetime datatype. If you go to the Timestamp section of the link below Microsoft explains it in detail. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms191240.aspx

|||

Hi

Thanks for your prompt response.

Let me give you background on the issue

There is a source system which is already developed from which i have to take incremental data every day and load into my warehouse.

There are no datetime fields in the source system for me to use and there is no scope for us to get the source system modified,

however each table is having the timestamp column which should give me daily new/updated records in the source system.

We can acheive this by storing the last loaded Timestamp in a control table and getting the latest timestamp using @.@.DBTS

Using this range we can get the incremental data from the source system.

This works fine.

However assuming that there is some issue with the source system and the database has to be restored from the backup.

Ideally i would have liked the @.@.DBTS to have the same value that was there when the backup was taken.

But the @.@.DBTS value is not coming the same infact it moves forward.

Logically @.@.DBTS moving forward should also be fine as I am interested in the records within the range and all the records will be included in the range.

So the question now is should I rely on @.@.DBTS even though it is different from the one which was there at the time of backup.

or am i better off to aviod this because of its unpredictable nature and build something to simulate the @.@.DBTS functionality i.e. Take max(timestamp) across all tables.

Please do let me know your views.

Cheers

Nishant hate