Performance - Int vs Char(3)

Question

I have a table and am debating between 2 different ways to store information. It has a structure like so

int id

int FK_id

varchar(50) info1

varchar(50) info2

varchar(50) info3

int forTable or char(3) forTable

The FK_id can be a foreign key to one of 6 tables so I need another field to determine which table it's for.

I see two solutions:

• An integer that is a FK to a settings table which has its actual value.
• A char(3) field with the a abbreviated version of the table.

I am wondering if anyone knows if one will be more beneficial speed wise over the other or if there will be any major problems using the char(3)

Note: I will be creating an indexed view on each of the 6 different values for this field. This table will contain ~30k rows and will need to be joined with much larger tables

Answer 1

In this case, it probably doesn't matter except for the collation overhead (A vs a vs ä va à)

I'd use char(3), say for currency code like CHF, GBP etc But if my natural key was "Swiss Franc", "British Pound" etc, I'd take the numeric.

3 bytes + collation vs 4 bytes numeric? You'd need a zillion rows or be running a medium sized country before it mattered...