Prepared query - plan cache
in [Server Configuration]
|
Prev: Failed to pause full-text catalog for backup. Backup was aborted
Next: How to create a new instance in SQL 2005 Server Standard?
From: Abba on 14 Oct 2009 05:11 O what a marvel! Now, its very much clear. Thank you Erland. I just tested it and what you have mentioned is correct. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CA4699C1F6F2Yazorman(a)127.0.0.1... > Abba (sql_help(a)aaa.com) writes: >> Thank you Erland. >> >>>> - Is the above query called a Prepared query? >>> >>> No. >> Then, is this also an ad-hoc query? > > I would call it parameterised query. An "ad-hoc query" really refers to > something that you run from outside an application, but there is not > really any strict definition. > >> EXEC sp_executesql >> @stmt = N'SELECT * FROM Inven.InventoryDetails WHERE InventID = @InvID', >> @params = N'@InvID int', @InvID = 100320 >> So, does this mean the plan is reused only if I send the same query as >> above. After the one above, if I send a query like this one below, >> wouldn't the query plan be reused? >> >> EXEC sp_executesql >> @stmt = N'SELECT * FROM Inven.InventoryDetails WHERE InventID = @InvID', >> @params = N'@InvID int', @InvID = 100397 > > No, the plan would be reused (assuming that it has not been flushed from > the cache for some reason.) On the other hand, this query would not > reuse the plan: > > EXEC sp_executesql > @stmt = N'SELECT * FROM Inven.InventoryDetails WHERE InventID = @InvID', > @params = N'@InvID int', @InvID = 100397 > > If you look closely, you will see that I've added a space in the parameter > list, and that's enough to get a cache miss. > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |