Relationship on a varchar field vs an int field

[Sashanan]

I've been having a discussion with a co-worker who feels that relationships between tables on a varchar type column should be avoided, as in his opinion they'll be much slower in query execution than on int type columns. In the database we're working on, we've currently got a varchar relation which we *might* be able to change to int with difficulty. I think it'll not be worth it in terms of speed, he thinks we should go for it. We're talking tables with maybe a couple thousand records in the foreseeable future.

Can anybody provide some insight here? Will there be a speed difference, and how big will it be? Will it be worth it for us to go through complicated changes, or are we gaining tenths of seconds?


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[skuhlman]

I don't believe you'll be gaining enough in processing to merit the effort on a record set of only a few thousand records. I also believe that making sure the columns are indexed would have a bigger effect than varchar .vs. int.

From the other perspective, using keys/ints to link tables and having the string value stored in a lookup table does make it so that if a string value changes, you only have to change one record instead of all of the records related to that value.

 

[Sashanan]

Thanks, that's what I thought. It's a non-changing value, fortunately, so in that sense it's suitable enough as a primary key.


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)