Compare timestamp type columns in SQL Server

Question

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 

Answer 1

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.

• DHAR: This works. I can't make changes to the database
• Aaron Bertrand: @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.
• DHAR: 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

Answer 2

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

Answer 3

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

• DHAR: I want all records after the last max record stamp.. this case its just 1
• jyao: 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.