DF047: A temporary table or table variable is declared but never used.

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

Message

A temporary table or table variable is declared but never used.

Description

Avoid having unused temporary tables and table variables in your code.

Additional information

Having unused temporary tables or table variables can lead to unnecessary resource consumption and may impact overall system performance. Additionally, it can introduce confusion for other developers who may review or maintain the code in the future.

Noncompliant code example

CREATE PROCEDURE dbo.DemoProc
  WITH RECOMPILE
AS
BEGIN
  SET NOCOUNT ON;
  /*declared but not used*/
  DECLARE @temp_table TABLE (
    LocationID SMALLINT NOT NULL,
    NameLocation NVARCHAR(255) NOT NULL
  );
 
  SELECT L.LocationID,L.Name
  FROM Production.Location l;
END;
GO
 
/*declared but not used*/
DECLARE @temporary_table TABLE (
  LocationID SMALLINT NOT NULL,
  NameLocation NVARCHAR(255) NOT NULL
);
GO

Compliant solution

CREATE PROCEDURE dbo.DemoProc
  WITH RECOMPILE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @temp_table TABLE (
    LocationID SMALLINT NOT NULL,
    NameLocation NVARCHAR(255) NOT NULL
  );
 
  INSERT INTO @temp_table (LocationID,NameLocation)
    SELECT L.LocationID,L.Name
    FROM Production.Location l;
END;
GO
 
DECLARE @temporary_table TABLE (
  LocationID SMALLINT NOT NULL,
  NameLocation NVARCHAR(255) NOT NULL
);
 
INSERT INTO @temporary_table (LocationID,NameLocation)
  SELECT L.LocationID,L.Name
  FROM Production.Location l;
GO