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

Last modified: December 25, 2024

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

Category

BEST PRACTICE

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

Want to find out more?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by SQL Complete.

All features

All features

Get acquainted with the rich features and capabilities of the tool in less than 5 minutes.

Request a demo

Request a demo

If you consider employing this tool for your business, request a demo to see it in action.

SQL Complete logo

Ready to start using  

SQL Complete?

Download a FREE 14-day trial