d_sysuk (4/29/2008)
There is a more subtle point, Im making in the query plan optimisation that is saved.A greater man, than I has quite nicely explained this , have a quick look at ( a google search should pull up the resource info):
MSDN I smell a parameter. Tips, Tricks, and Advice from the SQL Server Query Optimization Team
MSDN Ken Henderson plan stability in SQL Server 2000
It goes into a lot more detail than I can in this forum.
The example posted was quite cut-down, within the given example a name lookup is used to get the PK, which is then used to get the result set back. (2 Querys onto the source table - when this should really be a single set-based query utilising the name as in posters article to get the desired result-set, without the need to lookup the ID value then assign to a local var then use the local var value as a lookup )
I think you may have partially misinterpreted the articles you cite. I just read them, and what you outlined in your example isn't actually what's gone over in the articles.
Yes, setting a default can override certain aspects of query optimization. My prefered method for doing this is creating separate procs called by a "meta-proc".
What you have in your example doesn't actually accomplish this. I tested it, and the execution plans are identical, except for the name of the variable in the scalar operator in the seek predicates.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon