Home

INSERT statements should explicitly list the columns to be set

Description

    The "INSERT statements should explicitly list the columns to be set" rule states that when inserting data into a table, the columns that are being set should be explicitly listed in the INSERT statement. This ensures that the data is being inserted into the correct columns, and that no data is being inserted into columns that are not intended to be set. This rule also helps to prevent any potential errors that may occur if the wrong data is inserted into the wrong column.

Key Benefits

  • Explicitness: INSERT statements should explicitly list the columns to be set, ensuring that only the intended columns are affected.
  • Data Integrity: By explicitly listing the columns to be set, the data integrity of the table is preserved, as it ensures that no unexpected changes are made to the table.
  • Performance: Explicitly listing the columns to be set can improve performance, as it reduces the amount of data that needs to be processed.

 

Non-compliant Code Example

INSERT INTO Production.UnitMeasure   --Non compliant code (Columns list is missing)
VALUES (N'FT', N'Feet', '20080414');

Compliant Code Example

INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate)    --Compliant code (Explicitly list the columns to be set)
VALUES (N'Square Yards', N'Y2', GETDATE());
Visual Expert 2024
 VETSQLRULE11