I inherited an Access database with a main table of ledger balances that was setup as such:
DataPullNo, Company, Year, Cost Center, Account, ComboKey, Amount
The ComboKey is a text field and...
1. the Primary Key (even though Ive yet to find that field in any joins)
2. is populated with a concatenation of (DataPullNo & Company & Cost Center & Account)
3. is indexed (no duplicates)
-The table has about 2mil records.
-There are indexes on DataPullNo (DuplicatesOk) , and ComboKey(no duplicates)
-There are multiple data pulls in any given year (actuals, forecasts, etc) hence the datapull number field
-looking at the data, I can get a unique record on just (DataPullNo, CostCenter, Account)
A professional developer set this database up years ago. Why would anyone create this lengthy ComboKey field instead of just creating a Composite Primary Key? Ive read that the Primary Key sets up an ordered index in the background to facilitate lookups. Is that why he did it?
Would I get a performance increase if I create a composite key?