Posted by Erland Sommarskog on 10/01/73 11:25

--CELKO-- (jcelko212@earthlink.net) writes:
> I thought that 2005 was geting a "multi-plan" feature like DB2.

That does not sound familiar. (Actually, already SQL 2000 have multiple
plans for a query, but this is related to the setting of SET options.)

> It actually saves multiple exection plans and effectively does what AK
> proposed, but without you having to tell it. Do you know if the OPTION
> (RECOMPILE) discards or saves prior plans?

Since the hint instructs SQL Server to recompile the query each time, it
would be pointless to save the plan. (Compare stored procedure saved
WITH RECOMPILE; the plans for these are never in cache.)

OPTION (RECOMPILE) should be great for these dynamic search queries
where the user specifies the last name on one search and the next time
the shoe size.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]