O'Reilly logo

Microsoft® SQL Server 2012 Unleashed by Alex T. Silverstein, Chris Gallelli, Paul T. Bertucci, Ray Rankins

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Forcing Recompilation of Query Plans

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:

IF EXISTS ( SELECT * FROM sys.procedures               WHERE schema_id= schema_id('dbo')                 AND name = N'price_range')   DROP PROCEDURE dbo.price_rangeGOcreate proc price_range    (@low money, @high money)asselect * from Production.Product  where ListPrice between @low and @highreturn

Assume that a nonclustered index exists on the ListPrice column in the Product table. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required