SQL Server : verify that two columns are in same sort order

Question

I have a table with an ID and a date column. It's possible (likely) that when a new record is created, it gets the next larger ID and the current datetime. So if I were to sort by date or I were to sort by ID, the resulting data set would be in the same order.

How do I write a SQL query to verify this?

It's also possible that an older record is modified and the date is updated. In that case, the records would not be in the same sort order. I don't think this happens.

I'm trying to move the data to another location, and if I know that there are no modified records, that makes it a lot simpler.

I'm pretty sure I only need to query those two columns: ID, RecordDate. Other links indicate I should be able to use LAG, but I'm getting an error that it isn't a built-in function name.

In other words, both https://dba.stackexchange.com/questions/42985/running-total-to-the-previous-row and Is there a way to access the "previous row" value in a SELECT statement? should help, but I'm still not able to make that work for what I want.

Answer 1

One method uses window functions:

select count(*)
from (select t.*,
             row_number() over (order by id) as seqnum_id,
             row_number() over (order by date, id) as seqnum_date
      from t
     ) t
where seqnum_id <> seqnum_date;

When the count is zero, then the two columns have the same ordering. Note that the second order by includes id. Two rows could have the same date. This makes the sort stable, so the comparison is valid even when date has duplicates.