Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Need to simulate vfp behaviour of indexing on expression
Message
De
06/08/2002 14:19:43
 
 
À
06/08/2002 10:57:14
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Conception bases de données
Divers
Thread ID:
00686569
Message ID:
00686667
Vues:
43
>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
Michael Levy
MCSD, MCDBA
ma_levy@hotmail.com
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform