SA0124 : Columns in COALESCE are not all the same data type

The COALESCE function should have arguments of the same data type to avoid unexpected results.

Description

The problem arises when the arguments passed to the COALESCE function in T-SQL are of differing data types. This can lead to unexpected data type conversion and potential data loss in SQL Server, as the function tries to implicitly convert arguments to the highest precedence data type. Ensuring arguments have the same data type helps maintain predictable behavior and accurate results.

For example:

SQL
Copy
1-- Example of a query with COALESCE using different data types
2SELECT COALESCE(123, '456');

In this example, SQL Server will implicitly convert the string ‘456’ to an integer, which could cause errors or data truncation if the string contains non-numeric characters. Furthermore, the data type precedence may lead to inefficient query execution or unexpected clustering on keys.

  • Potential loss of data integrity due to implicit conversions.

  • Increased risk of runtime errors or exceptions in large queries.

  • Performance issues from implicit type conversions, slowing down execution.

How to fix

To ensure consistent and expected results when using the COALESCE function, all arguments should be of the same data type.

Follow these steps to address the issue:

  1. Identify all uses of the COALESCE function within your SQL queries and stored procedures.

  2. Check the data types of each argument passed to the COALESCE function, and ensure they are consistent. If they are not, proceed to standardize them to a common data type.

  3. Explicitly convert arguments to a suitable data type using CAST or CONVERT functions to ensure they are of the same type, thereby avoiding unexpected conversions.

  4. Test the query for correctness, verifying that the result is as expected and that there are no runtime errors due to improper type conversions.

For example:

SQL
Copy
1-- Before correction: potential error or data loss due to implicit conversion
2SELECT COALESCE(CAST(123 AS VARCHAR), '456');
3
4-- Corrected query: explicit conversion ensures predictable behavior
5SELECT COALESCE(CAST(123 AS VARCHAR), '456');

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 TABLE #wages
 2(
 3    emp_id        tinyint identity,
 4    hourly_wage   decimal   NULL,
 5    salary        money   NULL,
 6    commission    decimal(8,2)   NULL,
 7    num_sales     tinyint   NULL,
 8        info1                  nvarchar(123) NULL,
 9        info2                   varchar(122) NULL,
10);
11
12SELECT        COALESCE(hourly_wage * 40 * 52, 
13                             salary, 
14                                 commission * num_sales) AS 'Total Amount 1' ,
15                COALESCE(hourly_wage * 20 * 52,    
16                                 salary, 
17                                 commission * num_sales, 
18                                 commission , 
19                                 num_sales, 
20                                 info1,
21                                 info2,        
22                                 $41200.00,
23                                 '0.0000',  
24                                 '0') AS 'Total Amount 2' 
25FROM #wages
26ORDER BY 'Total Amount 1', 'Total Amount 2';
27
28DECLARE @Data xml 
29SET @data = EVENTDATA()
30
31SELECT coalesce(@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'int') ,1);
32SELECT coalesce(@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'char') ,1);
Analysis Results
  Message Line Column
1 SA0124 : Columns in COALESCE are not all the same data type.(decimal(38,0), money, decimal(12,2)) 12 7
2 SA0124 : Columns in COALESCE are not all the same data type.(decimal(38,0), money, decimal(12,2), decimal(8,2), tinyint, nvarchar(123), varchar(122), smallmoney, char(6), char(1)) 15 2
See Also

Other Resources

Analysis Rules