I am not a SQLServer guy.  We are moving our current VFP->DBF system to .NET->SQLServer and I have some table degsin questions.  In our VFP system we had a transaction code table with a 4 character code key field and a transaction table with the the transaction code (key for the code in the transaction code table) and other transaction specific details.  I want to maintain that same design but am hearing arguments that I should use an integer key and have the integer key in the transaction table.  Now I realize that integer keys should be faster when doing joins but my logic is that I don't have to even do a join on the table to get the code.

I should mention that the transaction codes are not arbitrary,  the values are like "PAY,NOT,NC" and the action of the transaction is determined by these specific code values, so if integer keys are used, any time I want to process a transaction I would have to lookup the code to determine the appropriate action.

I have several other tables that are in a similar type situation, like schedule codes and appointments.  So am I wrong here or should I use the character keys in situations like these.

Scott,

I use char keys in several situations similar to what you are describing and have not had any issues with using them speed or otherwise. Of course I don't have 20 million records and a website that is getting 10,000 hits a minute. I have almost 2 million recs in a table that has a compound (char(5) + char(10)) primary key and I am happy with the performance.

I guess it would depend on how big your data is and will become and what you mean by performance. You have existing data already so why not set up both scenarios and do some tests to see what the real difference is. To me a few milliseconds or even a second might not be that big a deal but you might think it is the end of the world.

Thanks for the input.  I think I will be using char keys for my lookup tables.

Scott