sql - Create a complex index on SQL Server

Question

Is it possible to somehow create an index on an expression so I can, for example, write a query against a field that is string, but meant to be numeric?

For example, I inherited a database that has a column PolarFactor which stores values like "234.28234" and is of type varchar(100).

I want to efficiently query that column, getting all rows with values in between 200 and 300.

Would be great if you could do something like this....

create index i1 on table(CAST(PolarFactor as Double))

This is not valid.

But I am wondering if either there is a way to do it, or if somebody has a better idea.

标签： sqlsql-serverindexing

Answer 1

Yes. Use a computed column:

alter table t add polarfactor_double as (try_cast(PolarFactor as double)) persisted;

create index i1 on t(PolarFactor_Double);

Note that I use try_cast() instead of cast(), so errors don't cause inserts to break.

Also, you should probably fix your data so PolarFactor is stored using the correct data type. Don't store numbers as strings.