• Xedni - Thursday, February 23, 2017 12:10 PM

    With all this, EXEC() made perfect sense. Targeted, as to not affect anything else on the database, planless, so as to skip the (albeit small) cost of creating then invalidating cache plans, and injection safe because it's configured by the developer.

    The only things that are 'planless' are DML statements that can only execute one way (eg CREATE TABLE). All DML has to have a plan generated in order to execute. Since SQL 2005, ad-hoc SQL and dynamic queries are treated identically to stored procedures in terms of their plans being cached.

    I'm not sure why you're looking to add recompile to that process. There's nothing in it that requires a fresh plan on every execution of the loop. Generating a plan the first time and reusing it on subsequent trips through the loop looks like the more optimal approach.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass