SA0126 : Operator combines two different types will cause implicit conversion

Introduction

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:

-- Example of problematic query
SELECT * 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:

-- Corrected version of the query
SELECT * 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.

Additional Information

Data type conversion (Database Engine)

Data type precedence (Transact-SQL)

CAST and CONVERT (Transact-SQL)

Example Test SQL

CREATE PROCEDURE testps_SA00126
@MinListPrice AS int,
@ModifiedBeforeDate AS nvarchar(20),
@Weight as int
AS

SELECT
     p.[ProductID]
    ,p.[Name] + p.[ProductID]
    ,pm.[Name] AS [ProductModel]
    ,pmx.[CultureID]
FROM [Production].[Product] p
    INNER JOIN [Production].[ProductModel] pm
    ON p.[ProductModelID] = pm.[ProductModelID]
WHERE
    p.ProductID > '110'
and CHARINDEX('500 Black',p.Name,0) > cast('9' as int)
and @Weight <= p.Weight
and cast(p.SellStartDate as Nvarchar(20)) <= dateadd(year, -1, getdate())
and @MinListPrice <= p.ListPrice
and 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

Analysis Rules