This question already has an answer here:
I have a situation where we have a non rdbms system that is replicating its data out to a SQL Server db. The way the data is defined in this other system the primary keys for many of the data types are created as varchars in SQL Server, even if the data is all numeric (integers). As we have been writing queries against this data we have seen terrible performance when joining across multiple tables using the varchar keys (that are numeric data but in varchar fields) as the join columns. After working through adding appropriate indexes and attempts at tweaking the queries they have not significantly improved.
As a test we recreated all of the tables, converting all of these key fields that were varchars into integers.
We ran the exact same query and we see significant improvement in the side with integer key columns (26 seconds for varchar keys, 4 seconds for integer keys).
Why are we seeing this situation? I was under the impression that as long as indexes were applied appropriately performance joining between tables would be very similar. Does it have something to do with the fact that numeric data stored as varchar won't sort properly? Other thoughts?