Optimizing SQL Server Query When Joining Tables on DATETIME column

I am working with SQL Server and I have two tables, table1 and table2. Both tables have a DATETIME column denoted as dt. I need to join these tables based not only on certain conditions but also to ensure the matching rows have the same date (time is not considered here) from the dt column.

Here's the query I'm currently using:
select *
From table1 a inner join table2 b
on a.id = b.a_id
and convert(date, a.dt) = convert(date, b.dt)


This query works to get the results I need, but I'm concerned about its performance, especially as the tables grow in size.

I'm wondering if there are more performant ways to achieve the same result? Are there techniques or SQL Server features that could help me optimize this query, especially the date comparison part?

Any advice or suggestions would be greatly appreciated. Thank you in advance.

computin'

If I were you and I had to do this repeatedly, I'd probably add computed columns that give you the data types you want, and index them accordingly.

ALTER TABLE table1 ADD dt_c AS CONVERT(date, dt);
ALTER TABLE table2 ADD dt_c AS CONVERT(date, dt);

Of course, this doesn't give you any real benefit on its own until the columns are indexed to support the join.

The nice thing about this approach is that adding the computed columns as non-persisted is a quick, non-blocking operation with near-zero writes to the database. You can defer that to when you add indexes (which you need anyway).

And via the expression matching portion of query optimization, you shouldn't even need to change the original query for SQL Server to use the new columns.

StackExchange Database Administrators Q#330027, answer score: 7