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.
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.
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.
For first example, let us first create one index on the
Date
column of thePerson.Person
table:CREATE INDEX IDX_Person_ModifiedDate ON Person.Person(ModifiedDate) GO
Now, if there is...