azure sql indexing strategy on datetime fields

Question

I have two datetimeoffset fields in table (sql azure), Migrated and Updated

I usually do this search select * from table where Migrated < Updated

What would be the right strategy for indexing these fields?

1. two indexes, one for each field
2. one index on Migrated, Updated
3. one index on Updated, Migrated

Table is huge and requires indexing...

Answer 1

In most versions of SQL Server, you can take the following approach. I have not tried this in SQL Azure.

Create a computed column and then an index on that:

alter table t add MigratedToUpdatedTime as (datediff(seconds, Migrated, Updated))

create index t_MigratedToUpdatedTime on t(MigratedToUpdatedTime);

Then in your where clause, you can do:

where MigratedToUpdatedTime > 0;