GilaMonster (10/16/2008)

---

Sure. This is the classic example

CREATE PROCEDURE AllOrSearch (

@SomeCode VARCHAR(30) = NULL

)

AS

IF @ID is null

SELECT * FROM SomeTable

ELSE

SELECT * FROM SomeTable WHERE SomeColumn = @SomeCode

If on the first execute, the parameter is null, then SQL will optimise both branches of the proc as if they would be run with the parameter of NULL. The second select (which won't actually be run with a parameter value of NULL) gets optimised as well and when the optimiser estimates rows affected, it will estimate 0 (Because no rows will ever match = NULL)

Now, let's say the optimal exec plan for a very few rows is an index seek with a bookmark lookup. That's what gets cached for the second query

Later, someone runs that with a value for @SomeCode that will return 1/4 of the table. Because the plan is cached, it will be reused, even though it's not an optimal plan for 1/4 of the table

Now this is a simplistic example, but the general principle is the same for more complex procs.