Mike,

I had mistakenly tried something like:

create table mytable (a int, b int)

create index myindex on mytable(a+b)

Regarding the design issues and optimization, for now, I need to 'get it out the door', and I will revisit the design issues in a few weeks.

Thanks for your help.

David

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