Improving query performance by not using functions on predicate columns

Using a scalar function with column name in predicate would again make your condition non-sargable. It is really a heavy load on the query optimizer and consumes lot of resources. For as long as possible, try to use the alternate method and avoid using functions with column name in predicate, to achieve performance boosts from the indexes.

Getting ready

There is no automatic way to find this behavior; this is simply a manual process. You either keep this step in mind while developing the SQL script or while working on performance tuning projects.

How to do it...

There will be two different examples here, in this recipe. The first example will use the DATE function in predicate, and the second example will use string function in predicate.

1. For first example, let us first create one index on the Date column of the Person.Person table:

   CREATE INDEX IDX_Person_ModifiedDate ON Person.Person(ModifiedDate)
   GO
   

2. Now, if there is...