George Heinrich (2/28/2008)
Great article! I'm glad I read it, but now i'm having trouble writing one of my queries...I need to join 2 tables on smalldatetime columns. One of the tables stores the date and time and the other table doesn't. So table A has '2008-02-27 10:32:31' and table B has '2008-02-27 00:00:00' and I need to join them on table A date = table B date (ignoring the time info).
Normally I use:
...FROM
TableA JOIN TableB
ON Cast(Cast(TableA.DateColumn AS INT) AS Smalldatetime) =
Cast(Cast(TableB.DateColumn AS INT) AS SmallDateTime)
...
Will wrapping these columns in the JOIN clause also cause a degradation of performance like wrapping them in a where clause would?
Is there a more optimized way of writing this?
Thanks,
George
Yes, functions in the Join clause have the same effect as functions in the Where clause.
What I generally recommend, if possible, in this case, is add calculated columns to the tables, like so:
alter table TableA
add DateColumnYear as datepart(year, datecolumn),
DateColumnMonth as datepart(month, datecolumn),
DateColumnDay as datepart(day, datecolumn)
go
create index IDX_TableA_DateParts on dbo.TableA (DateColumnDay, DateColumnMonth, DateColumnYear)
Then do your join/where clauses on the calculated columns. Like:
select ...column list...
from dbo.TableA
inner join dbo.TableB
on TableA.DateColumnYear = TableB.DateColumnYear
and TableA.DateColumnMonth = TableB.DateColumnMonth
and TableA.DateColumnDay = TableB.DateColumnB
Alternately, if you don't often select by year, month, day, but do often join on the whole date, you can add a calculated column:
alter dbo.TableA
add DateColumnDate as cast(convert(varchar(25), DateColumn, 102) as datetime)
go
create index IDX_TableA_DateColumnDate on dbo.TableA (DateColumnDate)
Do that to both tables, then join on the calculated column.
In my experience, this is very fast and efficient. Of course, it only applies if you are in a position to add calculated columns to tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon