SA0268 : Table variable is not used as table source

Declaring table variables without using them in subsequent SELECT, UPDATE, DELETE, or MERGE statements leads to unnecessary resource consumption and potential oversight in script logic.

Description

In T-SQL code, table variables are often declared for temporary data storage within scripts. However, if a table variable is declared and not utilized in any FROM clause of SELECT, UPDATE, DELETE, or MERGE statements that follow, it indicates unnecessary resource consumption and potential oversight in script logic.

For example:

SQL
Copy
1-- Example of unused table variable
2DECLARE @TempTable TABLE (ID INT);
3-- No subsequent SELECT, UPDATE, DELETE, or MERGE uses @TempTable

This is problematic because:

  • It results in wasted memory and processing resources, as the declared table variable is maintained without serving any functional purpose.

  • It can reflect logical errors or missed opportunities for optimizing data processing within the script, potentially leading to inefficiencies or incorrect results.

How to fix

To resolve the issue of unused table variables in T-SQL scripts, identify and eliminate any unnecessary table variable declarations to optimize resource usage and improve script logic.

Follow these steps to address the issue:

  1. Review the script to identify any DECLARE statements for table variables.

  2. Check for usage of these table variables in subsequent SELECT, UPDATE, DELETE, or MERGE statements. If they are not used, consider removing them.

  3. Refactor the script to eliminate any unused table variables to improve performance and clarity of your T-SQL code.

For example:

SQL
Copy
1-- Example of removing unused table variable
2-- Original script with unused table variable
3DECLARE @TempTable TABLE (ID INT);
4-- No subsequent operation uses @TempTable
5
6-- Revised script without the unused declaration
7-- Simply remove the unnecessary declaration

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
3 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1DECLARE @Greeting TABLE
 2(
 3GreetingId INT IDENTITY (1,1) PRIMARY KEY,
 4Message nvarchar(255) NOT NULL
 5);
 6
 7SELECT * INTO #Greeting2 FROM dbo.Greeting
 8
 9INSERT INTO @Greeting (Message) 
10SELECT 'Hello!'
11UNION ALL 
12SELECT 'Hi!'
13UNION ALL
14SELECT 'Hello, world!'
15
16DELETE  @Greeting WHERE GreetingId = 3
17
18-- SELECT * FROM @Greeting g WHERE g.Message like 'Hello%'
Analysis Results
  Message Line Column
1 SA0268 : Table variable is not used as table source. 1 8
See Also

Other Resources

Analysis Rules