SA0020 : Always use a column list in INSERT statements

When using an INSERT statement, not specifying the target column list can lead to various issues.

Description

Failing to specify the columns when performing an INSERT operation can pose significant challenges. This oversight makes SQL scripts error-prone, especially when the underlying table structure is modified.

Example of problematic query:

SQL
Copy
1INSERT INTO Employees VALUES (1, 'John Doe', 'HR');

The above query assumes the table column order remains constant. If a new column, say ‘Email’, is added at the beginning, this query could fail or insert incorrect data.

  • Potential for data integrity issues or runtime errors if table structure changes (e.g., adding or dropping columns).

  • Decreased code maintainability, as assumptions about the column ordering increase the risk of errors.

How to fix

Explicitly specify the target column list in the INSERT statement to ensure code resilience and maintainability.

Follow these steps to address the issue:

  1. Identify the target table and examine its column schema using SSMS or by querying the INFORMATION_SCHEMA.COLUMNS view.

  2. List all columns explicitly in the INSERT statement that will receive data, ensuring they match the order of values provided.

  3. Adjust the VALUES list to correspond with the specified column order, respecting data types and constraints.

Example of corrected query:

SQL
Copy
1INSERT INTO Employees (EmployeeID, Name, Department)
2VALUES (1, 'John Doe', 'HR');

Scope

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

Parameters
Name Description Default Value
IgnoreTempTargetTables

Ignore targets that are temporary tables or table variables.

yes
Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix
2 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE PROCEDURE HumanResources.uspGetEmployees 
 2    @LastName nvarchar(50), 
 3    @FirstName nvarchar(50),
 4        @JobTitle nvarchar(50),
 5        @Department nvarchar(50)
 6AS 
 7
 8    SET NOCOUNT ON;
 9
10        -- Target columns are not provided
11        INSERT INTO HumanResources.vEmployeeDepartment
12        VALUES (@FirstName,@LastName,@JobTitle,@Department)
13
14        INSERT INTO #EmployeeDepartment
15        VALUES (@FirstName,@LastName,@JobTitle,@Department)
16
17        -- Target columns are explicitly provided
18        INSERT INTO HumanResources.vEmployeeDepartment(FirstName, LastName, JobTitle, Department)
19        VALUES (@FirstName,@LastName,@JobTitle,@Department)
20
21        INSERT INTO HumanResources.vEmployeeDepartment                /*IGNORE:SA0020*/
22        VALUES (@FirstName,@LastName,@JobTitle,@Department)
Example Test SQL with Automatic Fix
SQL
Copy
 1CREATE PROCEDURE HumanResources.uspGetEmployees 
 2    @LastName nvarchar(50), 
 3    @FirstName nvarchar(50),
 4        @JobTitle nvarchar(50),
 5        @Department nvarchar(50)
 6AS 
 7
 8    SET NOCOUNT ON;
 9
10        -- Target columns are not provided
11        INSERT INTO HumanResources.vEmployeeDepartment (BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, JobTitle, Department, GroupName, StartDate)
12        VALUES (@FirstName,@LastName,@JobTitle,@Department)
13
14        INSERT INTO #EmployeeDepartment
15        VALUES (@FirstName,@LastName,@JobTitle,@Department)
16
17        -- Target columns are explicitly provided
18        INSERT INTO HumanResources.vEmployeeDepartment(FirstName, LastName, JobTitle, Department)
19        VALUES (@FirstName,@LastName,@JobTitle,@Department)
20
21        INSERT INTO HumanResources.vEmployeeDepartment                /*IGNORE:SA0020*/
22        VALUES (@FirstName,@LastName,@JobTitle,@Department)
Analysis Results
  Message Line Column
1 SA0020 : Always use a column list in INSERT statements. 11 1
See Also

Other Resources

Analysis Rules