Optimizing OR logic

A common query pattern involves the need to express several conditions of which at least one must be true to filter the result set, usually with OR logic. Expressing these OR conditions can have serious performance drawbacks and can often be replaced with other constructs that provide better scalability and performance.

The following example shows a query executed in the AdventureWorks sample database that uses an OR condition in the search predicate. The query lists all rows for a specific product, or where the price is set at a predetermined value:

SELECT ProductID, [UnitPrice], [UnitPriceDiscount],
      [UnitPrice] * (1 - [UnitPriceDiscount]) AS FinalUnitPrice,
      [UnitPriceDiscount] + 0.10 AS NewUnitPriceDiscount,
      [UnitPrice] * (1 - 0.30) AS NewFinalUnitPrice
FROM Sales.SalesOrderDetail
WHERE ProductID = 770
     OR UnitPrice = 3399...