Does an index speed the calculation of a DATEDIFF of two datetime values?

Question

I have a table with three columns:

id | start_date | end_date

I have a view that shows id and DATEDIFF(hour, start_date, end_date):

id | timespan

Will putting an index on the date columns speed that query? Does this only work with DATEADD? If so, why? My understanding is that for arithmetic queries like this, a TABLE SCAN is required.

Answer 1

No. It does not.

You can create a computed column and create an index on that:

alter table <table> add duration as datediff(hour, start_date, end_date);

create index on <table>(duration);

Of course, the index will be most useful if duration is used in where clause, an on clause, or order by.