>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.

Here's an example of a table with a computed column and how to create an index on the computed column.

CREATE TABLE myTable (
 a int
 ,b int
 ,c AS a + b )
GO
create index c on #mytable(c)

>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.

That is correct.

Interesting problem you have there. At first thought, I like the idea of the associated attribute table.

>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.

At least with this scheme you can provide a sold index configuration and you should be able to produce queries that are optimizable.

-Mike