Is it possible to create a non clustered index on column length or any sql function

Question

I've searched over this website and I've looked over similar questions and i did not find the answer, I am sure it is somewhere but i did not find it, my problem is, i have a table called Maps, this tables contains millions and millions of rows and this table got indexes : a ClusteredIndex on imagename column and a NonClusteredIndex on imagename column and downloaded column,

The following query takes 0s to execute :

SELECT top 100 imagename 
from [maps] 
where imagename='SomeExistingImageName' 
and downloaded is null

Result ->100rows

Also, this following query takes 0s to execute :

SELECT top 100 imagename 
from [maps] 
where imagename='SomeRandomNameThatDoesNotExistOnMyDatabase' 
and donwloaded is null

Result ->0rows

But when i try to run this following query, it takes 02min08s to execute:

SELECT top 100 imagename 
from [maps] 
where LEN(imagename)=10
and downloaded is null

Result -> 0rows (because there is no such imagename that fulfills those conditions)

Another example, when I change 10 to 17 it takes 0s to execute :

SELECT top 100 imagename 
from [maps] 
where LEN(imagename)=17
and downloaded is null

Result ->100rows

My question is what it is the best way to improve the performance of this kind of query (query with column length) ? Is it possible to create another NonClusteredIndex on LEN(imagename) ? if the answer is YES which columns should be involved ?

PS : my table contains over 500M entries

Answer 1

This one worked for me :

alter table [maps] add len_imagename as (len(imagename));
create index [maps] on [maps] (len_imagename) include([downloaded]);

The execution time now of the following query is 0s :

SELECT top 100 imagename 
from [maps] 
where LEN(imagename)=10
and downloaded is null

Answer 2

You can use virtual column

alter table [maps] add len_imagename as (len(imagename))
create index maps_ix_len_imagename on [maps] (len_imagename)

---

Demo

;with t(i) as (select 1 union all select i+1 from t where i<10)
select  replicate('x',rand(cast(newid() as varbinary))*1000)    AS i
into    #t
from    t t0,t t1,t t2,t t3,t t4,t t5,t t6
option  (maxrecursion 0)

alter table #t add len_i as (len(i))

create index #t_ix_len_i on #t (len_i)

select count(*) from #t where len_i between 99 and 101