T-SQL Analyzer

# SQL Server Rule: SRP0022 | | | |----|----| | Assembly | SqlServer.Rules | | Namespace | SqlServer.Rules.Performance | | Class | ConsiderRecompileQueryHintRule | ## Rule Information | | | |----|----| | Id | SRP0022 | | Friendly Name | Procedure level recompile option | | Category | Performance | | Ignorable | true | | Applicable Types | Procedure | ## Description Consider using RECOMPILE query hint instead of the WITH RECOMPILE option. ## Summary Consider using <c>RECOMPILE</c> query hint instead of <c>WITH RECOMPILE</c> option ### Examples good: ```sql CREATE PROCEDURE dbo.my_proc BEGIN SELECT col_A, col_b FROM some_complicated_set WHERE some_complicated_filter = 1 OPTION(RECOMPILE) ``` bad: ```sql CREATE PROCEDURE dbo.my_proc WITH RECOMPILE ``` ### Remarks The rule checks that stored procedures do not use <c>WITH RECOMPILE</c> procedure option. The <c>OPTION(RECOMPILE)</c> is the preferred method when a recompile is needed. The <c>WITH RECOMPILE</c> procedure option instructs the Database Engine does not cache a plan for this procedure and the procedure is compiled at run time. _{^{Generated by a tool}}