title: Execution Plan Optimization when where clause is removed then added back
body: <p>I have a stored procedure that uses a table valued function which executes in 9 seconds. If I  alter the table valued function and remove the where clause, the stored procedure executes in 3 seconds. If I add the where clause back, the query still executes in 3 seconds. </p>
  
  <p>I took a look at the execution plans and it appears that after I remove the where clause, the execution plan includes parallelism and the scan count for 2 of my tables drops for 50000 and 65000 down to 5 and 3. After I add the where clause back, the optimized execution plan still runs unless I run DBCC FREEPROCCACHE.</p>
  
  <p>Questions
  1. Why would SQL Server start using the optimized execution plan for both queries only when I first remove the where clause?</p>
  
  <ol>
  <li>Is there a way to force SQL Server to use this execution plan?</li>
  </ol>
  
  <p>Also, this is a paramaterized all-in-one query that uses the (Parameter is null or Parameter) in the where clause, which I believe is bad for performance.</p>
  
  <pre><code>RETURNS TABLE 
  AS
  RETURN 
  (
  SELECT  TOP (@PageNumber * @PageSize)
                  CASE
                      WHEN @SortOrder = 'Expensive' THEN ROW_NUMBER()     OVER (ORDER BY SellingPrice DESC)
                  WHEN @SortOrder = 'Inexpensive' THEN ROW_NUMBER() OVER (ORDER BY SellingPrice ASC)                  
                  WHEN @SortOrder = 'LowMiles' THEN ROW_NUMBER() OVER (ORDER BY Mileage ASC)
                  WHEN @SortOrder = 'HighMiles' THEN ROW_NUMBER() OVER (ORDER BY Mileage DESC)
                  WHEN @SortOrder = 'Closest' THEN ROW_NUMBER() OVER (ORDER BY P1.Distance ASC)       
                  WHEN @SortOrder = 'Newest' THEN ROW_NUMBER() OVER (ORDER BY [Year] DESC)    
                  WHEN @SortOrder = 'Oldest' THEN ROW_NUMBER() OVER (ORDER BY [Year] ASC)                     
                  ELSE ROW_NUMBER() OVER (ORDER BY InventoryID ASC)
              END as rn,
              P1.InventoryID,
              P1.SellingPrice,
              P1.Distance,
              P1.Mileage,
              Count(*) OVER () RESULT_COUNT,
              dimCarStatus.[year]
      FROM    (SELECT InventoryID, SellingPrice, Zip.Distance, Mileage, ColorKey, CarStatusKey, CarKey FROM facInventory
                  JOIN @ZipCodes Zip
                  ON   Zip.DealerKey = facInventory.DealerKey) as P1
      JOIN    dimColor
              ON dimColor.ColorKey = P1.ColorKey
      JOIN    dimCarStatus
              ON dimCarStatus.CarStatusKey = P1.CarStatusKey  
      JOIN    dimCar
              ON dimCar.CarKey = P1.CarKey                        
      WHERE
              (@ExteriorColor is NULL OR dimColor.ExteriorColor like @ExteriorColor) AND
              (@InteriorColor is NULL OR dimColor.InteriorColor like @InteriorColor) AND
              (@Condition is NULL OR dimCarStatus.Condition like @Condition) AND
              (@Year is NULL OR dimCarStatus.[Year] like @Year) AND
              (@Certified is NULL OR dimCarStatus.Certified like @Certified) AND
              (@Make is NULL OR dimCar.Make like @Make) AND
              (@ModelCategory is NULL OR dimCar.ModelCategory like @ModelCategory) AND    
              (@Model is NULL OR dimCar.Model like @Model) AND
              (@Trim is NULL OR dimCar.Trim like @Trim) AND
              (@BodyType is NULL OR dimCar.BodyType like @BodyType) AND
              (@VehicleTypeCode is NULL OR dimCar.VehicleTypeCode like @VehicleTypeCode) AND
              (@MinPrice is NULL OR P1.SellingPrice &gt;= @MinPrice) AND
              (@MaxPrice is NULL OR P1.SellingPrice &lt; @MaxPrice) AND
              (@Mileage is NULL OR P1.Mileage &lt; @Mileage)
      ORDER   BY
              CASE
                  WHEN @SortOrder = 'Expensive' THEN -SellingPrice
                  WHEN @SortOrder = 'Inexpensive' THEN SellingPrice 
                  WHEN @SortOrder = 'LowMiles' THEN Mileage
                  WHEN @SortOrder = 'HighMiles' THEN -Mileage
                  WHEN @SortOrder = 'Closest' THEN P1.Distance        
                  WHEN @SortOrder = 'Newest' THEN -[YEAR]
                  WHEN @SortOrder = 'Oldest' THEN [YEAR]                  
                  ELSE InventoryID 
              END
  )
  </code></pre>