Tip: Never use functions/Calculations/Formula on the left side of Where clause or in Join Predicate.
Let’s execute Below two queries which looks almost same
--Query 1 SELECT TransactionID ,ProductID ,TransactionType ,Quantity ,ActualCost FROM Production.TransactionHistory WHERE ActualCost / 2 > 50.00 --Query 2 SELECT TransactionID ,ProductID ,TransactionType ,Quantity ,ActualCost FROM Production.TransactionHistory WHERE ActualCost > 50.00 * 2
Query#1 has condition AS WHERE ActualCost / 2 > 50.00
Query#2 has condition AS WHERE ActualCost > 50.00 * 2
Logically, both are same and returns the same result, but Execution wise, they are completely different and these queries have a huge difference in terms of performance.
Let’s create the Index first
USE [AdventureWorks2017] GO CREATE NONCLUSTERED INDEX [IX_ActualCost] ON Production.[TransactionHistory] ([ActualCost]) INCLUDE ( TransactionID ,[ProductID] ,[TransactionDate] ,[TransactionType] ,[Quantity] ) GO
Now Execute the Query and check the Execution plan
As you can see, First query which is using formula at the left side of where clause has used Index scan and has a higher cost (82%) while the second query which is not doing any calculation at the left side of where clause has used Index seek and has much lower cost(18%) .
The reason of Index scan in first execution plan is Index has the data of ActualCost but it does not have any data of ActualCost/2 so it has to check the value of ActualCost/2 to satisfy the condition.
YouTube : Sql depository