Another solution involves creating two new stored procedures that an outer procedure calls, depending on which plan is best. For the examples so far, it would be difficult to predict without running the query which parameters will require which plan because each customerID value occurs a different number of times. For other types of queries, there might be a solution. If you had a range query where the parameter was the endpoint of the range, you might be able to predict that endpoints at the low end of the range would return only a few rows. Thus, using the nonclustered index would be a good plan, but for endpoints higher in the range, a different plan would be better.
The code in Listing 4 rewrites the getorders procedure again and creates two sub-procedures. SQL Server now calls the outer procedure, getorders, when you want to return all the rows that have a customerID value less than the input parameter. For this small amount of data in the Orders table, SQL Server could get faster performance with any value greater than or equal to ANTON by performing a table scan. Note that the two nested procedures are identical. However, the outer procedure calls the first one with a very selective parameter and the second with a not-so-selective parameter, so the plan for the second one will use a table scan. You can test the new procedure with the following batch, keeping in mind that you're now returning all orders for customers whose ID is less than the parameter:
EXEC getorders 'ALFKI'
EXEC getorders 'RANCH'
Again, you get an appropriate plan for both parameters, as you can see by the number of logical reads.
This solution isn't perfect, either. It's useful only when you can guess up front which actual parameters SQL Server will treat one way and which it will treat another way, but it doesn't account for changing the plan when the data distribution changes. If you perform updates, inserts, and deletes to the Orders table and change the data distribution, so that now any customerID values less than BINGO should be treated as highly selective, you'll have to rewrite the getorders procedure. You have to constantly monitor performance and retest the procedures to make sure that you're still getting the best possible plan as the data changes.
Nobody's Perfect
How do you get the best of both worlds? You don't want to recompile when the existing plan is good, but you need to be able to recompile (and reoptimize) when the existing plan isn't so good. And, you don't want to have to recompile more of the procedure than necessary. There's no perfect solution. When SQL Server knows that an existing plan might not be efficient because of changes to metadata or statistics, it can automatically recompile. But if an existing plan gives bad performance just because you're using a different parameter, SQL Server can know it needs a different plan only by taking the time to reoptimize. The more you know about how to use T-SQL to your advantage for tuning and optimization, the better performance you'll achieve.