performance on the join

20 Jan 2006 12:30 AM

culam

I know that join on an integer field is faster than join on varchar field.
What if I cast the fields to integer within the join clause,
ex. JOIN CAST(tableA.field AS int) = CAST(tableB.field AS int)
would this speed up my query?

Thanks,
Culam

20 Jan 2006 12:36 AM

Aaron Bertrand [SQL Server MVP]

Not very likely, since -- even if there are indexes on these columns -- an
explicit conversion pretty much guarantees a scan.

Did you try it? Did you compare execution plans (both real and estimated),
cpu/reads/statistics i/o, etc?

If all the data is integer, why is the column still defined as varchar?

Show quote

"culam" <cu***@discussions.microsoft.com> wrote in message
news:0D47D119-344A-419E-A5EB-EA44B06F1F88@microsoft.com...
>I know that join on an integer field is faster than join on varchar field.
> What if I cast the fields to integer within the join clause,
> ex. JOIN CAST(tableA.field AS int) = CAST(tableB.field AS int)
> would this speed up my query?
>
> Thanks,
> Culam
>
>