SA0126 : Operator combines two different types will cause implicit conversion

The topic describes the SA0126 analysis rule.

Message

Operator combines two different types will cause implicit conversion

Description

The rule checks the SQL code for operators combining two expressions of different data types and cause implicit conversion.

When fields of different data types are joined on or compared, if they are not the same data type, one type will be implicitly converted to the other type.

Implicit conversion can lead to data truncation and to performance issues appears in query filter.

Note Note

The precision, scale and size of the data types are not considered in this version of the rule.
How to fix

Review the code and try to use compatible data types for the fields that are being compared to avoid data truncation.

Scope

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

Parameters
Name Description Default Value
OutputResolvedTypes

Outputs the types of the COALESCE parameters.

yes
IgnoreUnresolvedTypes

Specifies if to ignore an operator if one of the operand’s type cannot e resolved.

yes
Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Effort To Fix
20 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1CREATE PROCEDURE testps_SA00126
 2@MinListPrice AS int,
 3@ModifiedBeforeDate AS nvarchar(20),
 4@Weight as int
 5AS
 6
 7SELECT 
 8     p.[ProductID] 
 9    ,p.[Name] + p.[ProductID] 
10    ,pm.[Name] AS [ProductModel] 
11    ,pmx.[CultureID]
12FROM [Production].[Product] p 
13    INNER JOIN [Production].[ProductModel] pm 
14    ON p.[ProductModelID] = pm.[ProductModelID] 
15WHERE 
16    p.ProductID > '110' 
17and CHARINDEX('500 Black',p.Name,0) > cast('9' as int) 
18and @Weight <= p.Weight 
19and cast(p.SellStartDate as Nvarchar(20)) <= dateadd(year, -1, getdate())
20and @MinListPrice <= p.ListPrice
21and p.ModifiedDate < @ModifiedBeforeDate;
Analysis Results
  Message Line Column
1 SA0126 : Operator combines two different types will cause implicit conversion. (nvarchar,int) 9 14
2 SA0126 : Operator combines two different types will cause implicit conversion. (int,*char) 16 16
3 SA0126 : Operator combines two different types will cause implicit conversion. (nvarchar,datetime) 19 42
4 SA0126 : Operator combines two different types will cause implicit conversion. (datetime,nvarchar) 21 19
See Also

Other Resources

Analysis Rules