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.

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:
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.

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:
-
Identify all uses of the COALESCE function within your SQL queries and stored procedures.
-
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.
-
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.
-
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:
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');

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

Name | Description | Default Value |
---|---|---|
IgnoreNumericDecimalScaleTruncations |
Ignore data loss warning when source has higher scale than the scale of the target. |
no |

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


Design Rules, Bugs


SQL
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); |

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 |
