How to avoid your DB index

Do note the headline is written in jest! Of course if you create a database index, you should try to make use of it in order to allow your queries to return the results faster. However, if you write your query in such a manner as below:

select
a.field1, a.field2, b.field3   
from dbo.table1 a   
inner join table2 b 
on UPPER(a.field2) = UPPER(b.[field4])   
inner join table3 c   
on c.field1 = a.field1
Unless there is a computed column present in the DB table, this script will be performing the dreaded full table scan even if table indices were defined for table1.field2 and table2.field4! This comes about because of the use of the UPPER keyword which transforms the string into something that cannot be matched in the index.

Apparently in Oracle, it is still possible to create a function-based index to meet this shortfall, but in SQL Server you will need to specially create a computed column, then create an index specifically for that column in order to enjoy the benefits of an index!

Leave a Reply

Your email address will not be published.