I created a simple lookup table with these columns:
iid = auto-inc, primary key
cReference = varchar(30), reference to owner/entity
cCode = varchar(16), abbreviation used as FK in other tables
cDescription = varchar(64), description for cCode
I've got a primary index on iid (obviously), and I was going to add an index on cReference too since so many stored procs (responsible for retrieving lookup data) would use the cReference in the WHERE clause. My question is this: I should probably add a candidate index on cReference + cCode to make sure no duplicates get in, but is that the best indexing strategy as you see it? I wondered if the single IDX on just the cReference field was necessary since I was going to have another index on cReference + cCode. I believe that the latter would suffice in MySQL, but I'm not sure about SQL Server.
tia!
--Michael
iid = auto-inc, primary key
cReference = varchar(30), reference to owner/entity
cCode = varchar(16), abbreviation used as FK in other tables
cDescription = varchar(64), description for cCode
I've got a primary index on iid (obviously), and I was going to add an index on cReference too since so many stored procs (responsible for retrieving lookup data) would use the cReference in the WHERE clause. My question is this: I should probably add a candidate index on cReference + cCode to make sure no duplicates get in, but is that the best indexing strategy as you see it? I wondered if the single IDX on just the cReference field was necessary since I was going to have another index on cReference + cCode. I believe that the latter would suffice in MySQL, but I'm not sure about SQL Server.
tia!
--Michael