best way to Join use date field

Question

I know I read somewhere that using a function as a join for example year(date) to do a join on another table that has the year is not the best way to join.

I basically have a table with the year field with the value 2016 and another table with the actual date like 01/01/2016 which i am using year(date) to make a join.

Would it be good practice to create a field as year 2016 for instance would normally start in a financial period so to have a field for start date 01/04/2016 and end date 31/03/2016 and use these fields to do a join. But was wondering how would that look like in terms of a Join? or shall I carry on using my method of converting the 01/01/2016 to a year using the year() function?

Answer 1

No, don't do anything special. Your problem can be easily solved by reversing the problem.

The problem with your current implementation is that a function must be executed on every row of the joined table for every row in the main table, which has 2 nasty effects:

• it's slow. You must execute the function n x m times
• you can't use an index (if there was one, and there should be) on the datetime column of the joined table

But there's a simple way to avoid all that nastiness: Calculate the start and end of the year as datetimes from the year value and use it to join to the other table on its datetime being between these two values. This would:

• only calculate the start/end dates once per main table row
• allow an index to be used over the datetime column (you should add one if there isn't one already)

I don't really understand you question enough to use real code, so here's what a pseudo query would look like:

select *
from table1
join table2 on table2.datetime
  between <calculate start of year from table1.year>
  and <calculate end of year from table1.year>

Answer 2

One thing you can do is define a computed column and then create an index on that:

alter table t add start_year as (year(start_date));

create index idx_t_??_start_year on t(??, start_year);

The ?? is intended to be one or more other columns. An index only on start_year would not be very selective, so SQL Server might not use it.