Skip to content

SA0047 : Consider indexing the column as it is used in a WHERE clause or JOIN condition

This problem occurs when non-indexed columns are used in WHERE or JOIN clauses, potentially impacting query performance.

Using columns without indexes in WHERE or JOIN clauses can slow down query execution. An index allows SQL Server to quickly locate and retrieve the required data without scanning the entire table, similar to how an index in a book helps find information quickly.

For example:

-- Example of problematic query with non-indexed columns
SELECT * FROM Orders
WHERE CustomerID = 'ALFKI';

This query example fetches all orders for a specific customer. If the CustomerID column is not indexed, SQL Server must scan each row of the table to find matches, which is inefficient for large datasets.

  • Slower query performance due to non-indexed searches.

  • Increased CPU and I/O resource usage, leading to potential performance bottlenecks.

Optimize query performance by indexing columns used in WHERE clauses or JOIN conditions.

Follow these steps to address the issue:

1.Identify the columns used in WHERE clauses or JOIN conditions that are not indexed.

2.Evaluate the existing indexes on the table to ensure that the necessary columns are not already indexed.

3.Create an index on the identified columns if one does not exist:

4.Use the CREATE INDEX statement to create the index, ensuring you specify the correct table and columns.

5.Test the query performance after indexing to ensure improvement and no adverse impact on other operations.

For example:

-- Create an index on the CustomerID column
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);
-- Example of corrected query using the indexed column
SELECT * FROM Orders
WHERE CustomerID = 'ALFKI';

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

Name Description Default Value
MinimumParentTableRowCount The parameter specifies the minimum number of rows that the parent table of the indexed column must have for the rule to alert. This way small tables can be ignored. 1000
IgnoreColumnsFromTempTables The parameter specifies if to ignore columns of temporary tables or table variables. yes
IgnoreTableSourceWithAtLeastOneIndexedColumnInUse The parameter specifies if to ignore table sources that already have one indexed column used in the query filter. yes
ReportOnlyFirstNonIndexedColumnForTableSource The parameter specifies whether to report only the first not indexed column that is found for a particular table source or report all not indexed columns. yes

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

20 minutes per issue.

Performance Rules, Bugs

There is no additional info for this rule.

CREATE PROCEDURE [dbo].[Procedure3WithWarnings]
@param1 int
AS
SELECT *
FROM [Production].[BillOfMaterials] b
WHERE ( b.[ComponentID] + 5 > @param1) or b.[BOMLevel] > 5
SELECT *
FROM [Production].[BillOfMaterials] b
WHERE ( b.[ComponentID] > (@param1 - 5)) or b.[BOMLevel] = @param1
SELECT *
FROM [Production].[BillOfMaterials] b
WHERE ( b.[ComponentID] > (@param1 - 5)) or b.[BOMLevel] /*IGNORE:SA0047*/ = @param1
  Message Line Column
1 SA0047 : Consider indexing column [Production].[BillOfMaterials].[BOMLevel] as it is referenced in a WHERE clause or JOIN condition. 6 43

Analysis Rules