6

I have a table Com_Main which contains column CompanyName nvarchar(250). It has average length of 19, max length = 250.

To improve performance I want to add a computed column left20_CompanyName which holds the first 20 characters of CompanyName:

alter table Com_main 
add left20_CompanyName as LEFT(CompanyName, 20) PERSISTED

Then I create Index on this column:

create index ix_com_main_left20CompanyName 
on Com_main (LEFT20_CompanyName)

So when I use

select CompanyName from Com_Main
where LEFT20_CompanyName LIKE '122%'

it uses this nonclustered index, but when the query is like:

select CompanyName from Com_Main 
where CompanyName LIKE '122%'

It uses full table scan, and don't use this index.

So the question:

Is it possible to make SQL Server use this index on computable column in last query?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mikhail Padlesny
  • 63
  • 4

2 Answers2

1

No. MySQL supports partial indexing of varchar columns but MS SQL Server does not.

You might be able to speed up table scans through partitioning but I don't know how smart SQL Server is in this regard.

dwurf
  • 12,393
  • 6
  • 30
  • 42
1

I don't think the SQL query engine would realize that the LEFT20_CompanyName column maps over so neatly to the CompanyName column - since a computed column could use virtually any formula, there's no way for it to know that the index on that other column is actually useful in this case.

Why not just create the index on the CompanyName column? So what if a few values in that field are longer than average? If you create it directly on the column and avoid the computed column altogether, I think it will use the index in both cases.

Maybe I'm missing something, but I'm not sure what you're trying to gain by doing the computed column on only the first 20 characters.

SqlRyan
  • 33,116
  • 33
  • 114
  • 199
  • Yes, I think you are right, but the main goal was to check out if it is possible to use a computed column as an index. Of course I created an index on CompanyName column after all. I seems that MS SQL doesn't support partial indexing of varchar columns, so that is the answer. – Mikhail Padlesny Jan 10 '13 at 12:57