I understand that sql server does not allow for indexing on an expression. I have also read that sql 2000 supports indexing on a 'computed' column, but I have not seen examples of this, and haven't been able to come up with what I thought was a valid computed expression that would work.

Right now I have a table driven flexible design for doing lookups on an inventory item id, where the positions within the item code are meaningful (not the best design but..). I store lookup expressions in a table, such as:
substring(citemno,1,4) and use them in my where and orderby clauses.
But my understanding is that SQL server cannot optimize these statements.

One possible implementation here is that I could have ONE 250 character string in my item master table. Then have a template, per item, or item 'type', that defines up to 250 characters worth of potential lookup strings, with expressions in my template definition that define what position of the substring to look in for the attribute. These lookup strings would be for additional 'user defined' attributes beyond the attributes in the existing table.

However, obviously this is infeasible in terms of an optimized query. Alternatively, I could have, say, 10 25 character columns lookupexp1 through lookupexp10, then my expressions could be on the full column name, and I could predefine an index for each lookupexp column - although I hear that too many indexes is bad also. But this seems highly inefficient.

Finally, I guess I could have a separate table for 'additional attributes'.
The key for the item master table is citemno. The additional table could have:

citemno, AdditionalAttributeID, AttributeValue.

Another complication is that a distinction needs to be made between attributes that are just 'regular' attributes, and attributes that may be used to define and/or extend the primary key. E.g. if the company sells flowers - variety+size+color+grower uniquely identifies an item, but if they also sell paintings of flowers, length+width+artist uniquely identifies the item. The problem is that the entity used to store inventory items has subtypes, with varying characteristics that define the key.

Please bear with me on this - I do have a working knowledge of entity-relationship and normalization concepts, but I'm certainly not a database design guru.

Sorry for such a long post. I hope this is clear.


Any suggestions on how to design/implement this would be greatly appreciated.

TIA