troubleshooting Question

Composite Keys and Performance

Avatar of Davisro
DavisroFlag for United States of America asked on
Microsoft AccessMicrosoft SQL Server
8 Comments4 Solutions41 ViewsLast Modified:

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?


Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 4 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros