1

I am trying to get data since last timestamp. In my legacy database, all tables have a column named as recordStamp type timestamp. I want to rely on this to select records modified after the max(recordStamp) from previous select.

I tried with T-SQL; but this tells only equality. What are my options..

CREATE TABLE Cars (
    id int identity, 
    model varchar(20), 
    brand varchar(20), 
    recordstamp timestamp)

INSERT Cars (model)
VALUES 
('accord'),
('camry'),
('corolla'),
('civic'),
('prius')


--latest recordstamp: 0x0000000001DD8AAF
select * from cars order by recordstamp desc --latest recordstamp: 0x0000000001DD8AAF

--Update a record
Update Cars set brand = 'honda' where model = 'accord'

--I want only the accord in result. 
select * from cars order by recordstamp desc 
2

TIMESTAMP is easily the worst naming decision Microsoft has made, at least in SQL Server. Not only does the data not have any date or time information, using the name actually violated the ANSI standard.

I asked them long ago to deprecate the keyword and always refer to this as ROWVERSION. But still the tools and metadata show and script out TIMESTAMP even when you explicitly created the table using ROWVERSION. It's horribly misleading.

In any case, there is no way to derive date/time information from this column, or to tell how much time has passed when the column information changes by n. And I'm not sure how you're going to know what recordstamp to base it off of anyway, if you're trying to compare. In an isolated scenario you can do something like this:

SELECT *, CONVERT(int, recordstamp) 
  FROM dbo.Cars;

enter image description here

DECLARE @ts int;
SELECT @ts = MAX(convert(int, recordstamp)) FROM dbo.Cars;

UPDATE Cars SET model += 'x' WHERE id = 1;

SELECT *, CONVERT(int, recordstamp) 
  FROM dbo.Cars 
  WHERE recordstamp > @ts;

enter image description here

But how are you going to do this when it's another session updating the table? And outside of a very controlled environment, you have no idea what's going to happen if this database is backed up and restored elsewhere, fails over, or even when the instance is restarted. The documentation states that it is increasing, but doesn't explicitly state that this is forever, and also warns against using it for tracking date/time in any way.

If modification date is important to you, do it right: add a datetime2 column to the table and keep it maintained with a trigger.

  • This works. I can't make changes to the database – DHAR Apr 3 '18 at 20:06
  • 1
    @DHAR Correction: this works for now, and when everything happens in the same session (analyzing that has little value I think). I was not kidding about not being able to rely on this long term. – Aaron Bertrand Apr 3 '18 at 20:11
  • Yes.. this approach is not scaling well in production. Often the query to filter the CARS changed after a timestamp.. is taking 100% CPU.. what are my other alternatives – DHAR Sep 7 '18 at 19:57
0

This works for me

declare @Cars TABLE  (id int identity, model varchar(20),  brand varchar(20),  recordstamp timestamp);
INSERT @Cars (model)
VALUES 
('accord'),
('camry'),
('corolla'),
('civic'),
('prius');
select * 
from @cars 
order by recordstamp desc;
declare @TS timestamp = (select max(recordstamp) from @cars);
update @Cars set brand = 'honda' where model = 'accord';
select * 
from @cars 
order by recordstamp desc;
select * 
from @cars 
where recordstamp > @TS;

id          model                brand                recordstamp
----------- -------------------- -------------------- ------------------
5           prius                NULL                 0x00000000000007EB
4           civic                NULL                 0x00000000000007EA
3           corolla              NULL                 0x00000000000007E9
2           camry                NULL                 0x00000000000007E8
1           accord               NULL                 0x00000000000007E7


id          model                brand                recordstamp
----------- -------------------- -------------------- ------------------
1           accord               honda                0x00000000000007EC
5           prius                NULL                 0x00000000000007EB
4           civic                NULL                 0x00000000000007EA
3           corolla              NULL                 0x00000000000007E9
2           camry                NULL                 0x00000000000007E8


id          model                brand                recordstamp
----------- -------------------- -------------------- ------------------
1           accord               honda                0x00000000000007EC
-2

I think the following query will return you what you need

CREATE TABLE Cars (id int identity, model varchar(20), brand varchar(20), recordstamp timestamp)
go
INSERT Cars (model) VALUES ('accord'), ('camry'), ('corolla'), ('civic'), ('prius')
go
Update Cars set brand = 'honda' where model = 'accord';
go
select * from Cars
where  recordstamp = (select max(recordstamp) from cars)
go

enter image description here

  • 1
    I want all records after the last max record stamp.. this case its just 1 – DHAR Apr 3 '18 at 20:42
  • Ok, I got you, it means you need to know the "last" max recordstamp, and then you can use where recordstamp > @last_max_recordstamp to retrieve all records you want. – jyao Apr 3 '18 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.