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?

marked as duplicate by mustaccio, Max Vernon sql-server Jan 24 at 20:34

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Its good that you have done the experiment before asking question.Numeric data stored as varchar will sort properly.Firstly It has to do with Datalength or Varchar will occupy more data pages... you think in this line. – KumarHarsh Jan 25 at 8:04