In some situations, a stored procedure might generate different query plans, depending on the parameters passed in. At times, depending on the type of query and parameter values passed in, it can be difficult to predict the best query plan for all executions. Consider the following stored procedure:
Assume that a nonclustered index exists on the
advance column in the
titles table. A search in which
advance is between 1,000 and 2,000 might be highly selective, and the index statistics might indicate that fewer than 5% of the rows fall within that range, and thus an index would be the best way to find the rows. ...