SA0126 : Operator combines two different types will cause implicit conversion

Implicit data type conversions in SQL queries can lead to performance issues and unexpected behavior.

Description

Implicit data type conversion occurs when SQL Server automatically converts one data type into another during operations such as comparisons or joins. This is common when expressions involve different data types without explicit conversion.

For example:

SQL
Copy
1-- Example of problematic query
2SELECT * FROM Orders WHERE OrderID = '123';

In this query, OrderID is likely an integer, but it is being compared to a string. SQL Server will implicitly convert the string to an integer, which may result in performance overhead and could lead to errors or unexpected results if conversion fails.

  • Potential performance degradation due to extra processing required for conversion.

  • Risk of data truncation or conversion errors if incompatible types are involved.

How to fix

Ensure data type compatibility in SQL queries to prevent implicit conversions that may cause data truncation or performance issues.

Follow these steps to address the issue:

  1. Identify query expressions where different data types are being compared or involved in operations. Use sys.columns or view column definitions in SQL Server Management Studio (SSMS) to check data types.

  2. Modify the query to use compatible data types through explicit conversion functions like CAST or CONVERT, ensuring compatibility between compared fields.

  3. Replace string literals with values that match the data type of the column being compared. For example, if comparing an integer field, use integer literals instead of strings.

For example:

SQL
Copy
1-- Corrected version of the query
2SELECT * FROM Orders WHERE OrderID = CAST('123' AS INT);

Scope

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

Parameters
Name Description Default Value
IgnoreNumericDecimalScaleTruncations

Ignore data loss warning when source has higher scale than the scale of the target.

no
Remarks

The rule requires Analysis Context. If context is missing, 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 : The operator combines two different types, which can result in implicit conversion.(int to nvarchar(50)). 9 14
2 SA0126 : The operator combines two different types, which can result in implicit conversion.(int to decimal(8,2)). 18 12
3 SA0126 : The operator combines two different types, which can result in implicit conversion.(int to money). 20 18
4 SA0126 : The operator combines two different types, which can result in implicit conversion.(nvarchar(20) to datetime). 21 19
See Also

Other Resources

Analysis Rules