CODEWITHSUNDEEP
performancesql-server-2005calculated-columns
Mikhail Padlesny
Mikhail Padlesny

Reputation: 63

Computed column index

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?

Upvotes: 6

Views: 340

Answers (2)

dwurf
dwurf

Reputation: 12749

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.

Upvotes: 1

SqlRyan
SqlRyan

Reputation: 33914

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.

Upvotes: 1

Related Questions