July 20, 2022 at 8:35 am
I have created a new DB (no backups) 1 table with a few rows, 1 field is a ROWVERSION field named Vercol
When I run this, the 2 outputs are the same but when I run in the morning @@DBTS has moved on. Checking regularly during the day no change. (This is on local SQL2019 and my laptop is rebooted nightly, in case restarting SQL changes @@DBTS?)
Where can I see which element the new @@DBTS value relates to when the DB has 1 table only and it isn't there.
All comments say this field is for local database, which is not being modified or backed up etc. so no idea why @@DBTS is changing, and it makes using it impossible unless I have misunderstood what it should do.
This database only has this 1 table
DECLARE @x ROWVERSION
SET @X = (SELECT MAX(vercol) FROM dbo.[Table1])
SELECT @x AS MAXVerCol
SELECT @@DBTS AS DBTS
This is for a project to pull data down from a place that uses rowversion, so i just want to download whose RowVersion is > what I have locally.
So was going to run query like select fields from linkedserver.db.dbo.tableX where rowversionfield > @@DBTS for example.
But i think I will have to use the @X variable instead as more reliable
July 20, 2022 at 3:46 pm
What is the definition of table1?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 20, 2022 at 3:55 pm
thanks for the reply.
It is really simple isolated table, just for testing
PriKey INT, VerCol rowversion, FieldB varchar(50)
I have now also copied this to another box that does not get SQL restarted, I know this isn't the reason, but just to rule that out.
July 20, 2022 at 4:54 pm
thanks for the reply.
It is really simple isolated table, just for testing
PriKey INT, VerCol rowversion, FieldB varchar(50)
I have now also copied this to another box that does not get SQL restarted, I know this isn't the reason, but just to rule that out.
If your local table is simply being used to backup data from other tables, I suggest that you do not use ROWVERSION as the VerCol datatype. Instead use BINARY(8) or VARBINARY(8).
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 21, 2022 at 8:28 am
Hi Phil,
Thanks for the info and suggestion, yes the source table/field is a rowversion column and I need to query it to just pull back records (daily/hourly etc.) that have changed since my last download, so my local table I will make binary(8) sure, thanks.
So solution I am thinking of, will be something like this where the remote table has the real ROWVERSION field and my local table will be BINARY(8)
DECLARE @LatestChange binary(8)
SET @LatestChange = (Select MAX(VerCol) from localtable)
SELECT blah, etc From Linkedserver.DBname.dbo.RemoteTable WHERE RemoteVerCol > @LatestChange
Is using MAX reliable/preferred approach to this exercise. It looks safe and a perfectly sound approach but just to ask.
Was just very curious around @@DBTS as found it changes overnight when zero change to the only table in the database
July 21, 2022 at 8:43 am
Your suggested code looks fine to me. You could simplify it a little:
SELECT @LatestChange = MAX(VerCol) from localtable
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 21, 2022 at 8:51 am
Hi Phil,
Absolutely can do that subtle change, but thank you for confirming approach of MAX(field) is good when used against Rowversion.
I thought it would be but thanks.
July 21, 2022 at 8:54 am
No problem.
Are there any deletes happening in the remote table?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 21, 2022 at 8:58 am
No deletes, I've already asked that 🙂
They just set a status of the record to 'closed/lost/cancelled' etc.
It is curious though, if you
create a blank DB, create a single table with a ROWVERSION field, then insert 1 record and check max(rowversion field) and @@DBTS they match but tomorrow @@DBTS is higher.
July 21, 2022 at 9:05 am
From the ROWVERSION help:
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock.
I don't know exactly what that means, but the fact that the word 'time' is used is perhaps behind the reason for the behaviour you are seeing.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 21, 2022 at 9:09 am
Yes I guess the physical 'date' is somehow influencing @@DBTS, as the database is not changing at all.
I haven't checked at 11:59pm and 00:01am 🙂 just end of working day and start of working day
The way I interpreted it, was that it only changes on an INSERT/UPDATE
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply