March 8, 2002 at 8:27 am
Normally I use identity columns for indexes (if no integer candidate keys are available), but in this case, I am very much in doubt.
We need a currency table for a project. This will contain the ISO code for the currency and a name. The other DBA wants to use an Identity PK, I imagine it will be faster to use the ISO code, since we normally only display the ISO code from the linked tables, not the currency name.
My question is in two parts:
1) How much of a performance hit do you get with char(4) primary keys compared to int?
2) What method would you use in this case? Why? 🙂
TIA,
Lars Hulvej
March 8, 2002 at 10:00 am
1. at a low level, loading an integer into the CPU register is a one step operation. The comparison is the third (the second load is for the other integer). This means it's very fast and a good argument for using integers. The char(4), would take up to 4 loads, though in today's 32bit registers, this could be one load, so the difference might be negliable.
2. I'd use the ISO code. While identities are easy to use, there is no good reason not to use the ISO code as a PK. If you have a good PK in your data, I think it makes sense to use it, unless it's a large value. Like a varchar( 80) or something. Here the ISO code and the Identity would perform the same funciton and you'd be storing 8 bytes of info for every row when you only need 4. Not that the extra 4 will add up to much in terms of performance, but every little bit helps.
Steve Jones
March 8, 2002 at 11:13 am
Are you sure the ISO codes won't change, or at least very unlikely? Overall I agree with Steve, no reason not to use a good candidate for the pkey. Size matters not just for the pkey but how places you'll be storing it as a fkey too.
Andy
March 14, 2002 at 2:03 am
Lars,
I've always used the Currency code as the primary key. This way I don't have to join the currency table to my query, just to get the currency code.
But are you sure that you want to use Char(4) and not Char(3)?
Where I work, the ISO code is used most places, and that is only 3 char. We have realised that when importing data from other systems, this may not be the case, and therefore we've created a Currency_Alias table, to correct spelling mistakes.
We've also added a field called Active. This way we can stop uses using currencies that are no long valid, but historic data can be kept safe without problems.
More importantly; We have a field called NoOfDecimals. Some currencies do not have decimals, like JPY. Most use 2 decimal places like USD and DKK. BUT some use 3 decimal places!
If you are sure that you'll never sell your product to anyone dealing with Arabic countries, just ignore this fact. Otherwise; Add the field, change your database to store at least 3 digits after the dot.
Your name sound danish, if so, and if you want to discuss these currency problems; just give me a call on 40 54 44 55.
Kind regards
Henrik Staun Poulsen
Stovi Software,
Skanderborg, Denmark.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply