Post reply

Use of temporary tables in a stored procedure

  • October 28, 2013 at 5:03 pm

    #284360

    If we Use of temporary tables in a stored procedure, is it going to cause the stored procedure to be recompiled every time the procedure is executed?

  • October 29, 2013 at 4:38 am

    #1662272

    Hi,

    It depends.

    If it’s not cached then it will be recompiled the first time it’s executed. Then it should not recompile, unless some operations that cause recompilation are used.

    It can be recompiled in cases when you’re creating the tempdb objects dynamically, this means for e.g. when your temp table definition varies on other conditions.

    If you call another sp/fn inside your sp that uses temp tables, a recompilation is possible.

    It will recompile if you’re declaring cursors on temp tables.

    It will recompile if the temp table is filled with quite big amount of data.

    You can use OPTION (KEEP PLAN) to make temp table behaves same as permanent table.

    Having some SET commands will recompile the SP.

    If you perform schema changes to objects (including tempdb objects) in the SP then it will recompile.

    Regards,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply