Using arithmetic operator wisely in predicate to improve performance

Arithmetic operation directly on the column name in the WHERE condition makes your condition non-sargable and index defined on the column will not get performance benefits. As long as possible, try to avoid this situation by logical workarounds to gain performance boosts.

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.

Let us use two tables from the AdventureWorks2012 database to demonstrate this exercise.

• [AdventureWorks2012].[HumanResources].[Employee]

• [AdventureWorks2012].[HumanResources].[EmployeePayHistory]

How to do it...

Follow the steps given here to perform this recipe:

1. Select some basic details of an employee from the Employee table and get the latest the rate (considering rate as the hourly rate of an employee) from the EmployeePayHistory table. Suppose...