DF074: The WITH RECOMPILE option is used.

The topic describes the DF074 T-SQL code analysis rule.

Category

PERFORMANCE

Message

The WITH RECOMPILE option is used.

Description

Consider using the RECOMPILE query hint instead of the WITH RECOMPILE option.

Additional information

Using WITH RECOMPILE on a stored procedure causes the entire procedure to be recompiled every time it runs, which can lead to unnecessary overhead. In contrast, the RECOMPILE hint query hint allows you to recompile only specific queries within the procedure. This provides more granular control, helps avoid performance issues caused by parameter sniffing, and ensures that execution plans reflect current data distribution.

Noncompliant code example

CREATE PROCEDURE dbo.DemoProc
  @Id INT
  WITH RECOMPILE
AS
BEGIN
  SET NOCOUNT ON;
 
  SELECT c.FirstName, c.LastName
  FROM dbo.Customer c
  WHERE c.Id = @id;
END
GO

Compliant solution

CREATE PROCEDURE dbo.DemoProc
  @Id INT
AS
BEGIN
  SET NOCOUNT ON;
 
  SELECT c.FirstName, c.LastName
  FROM dbo.Customer c
  WHERE c.Id = @id
  OPTION(RECOMPILE);
END
GO