JSON Data Type Is Not Comparable!

I was really not happy when I hit this with the new JSON data type. In SQL Server, set operators like UNION, INTERSECT, and EXCEPT have been a very reliable part of many generic scripts. We already survived older non-comparable types like text and image, and things became much cleaner after moving away from them. But now JSON brings a very similar problem back. Even if both sides have the same column type, SQL Server still refuses to compare it in set operators.

Quick reminder: these operators need values that SQL Server can compare to detect duplicates and matches. If a data type is non-comparable, generic set-based scripts break immediately unless you cast. Most common troublemakers are legacy LOB types (text, ntext, image), and also xml in many compare/sort scenarios. And the new json since SQL Server 2025. So this is not only about one edge case, it is a design concern for reusable SQL tooling.

This is the simple repro:

DROP TABLE IF EXISTS [dbo].[TableA]
DROP TABLE IF EXISTS [dbo].[TableB]

CREATE TABLE [dbo].[TableA] (
    [JSON_Data] JSON
)

CREATE TABLE [dbo].[TableB] (
    [JSON_Data] JSON
)

SELECT * FROM [dbo].[TableA]
UNION
SELECT * FROM [dbo].[TableB]
GO

It fails with this SQL Server error:

Msg 5335, Level 16, State 1, Line 14
The data type json cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

That means generic scripts now need special handling whenever any source column is JSON.

Current practical fix is explicit cast:

SELECT CAST([JSON_Data] AS NVARCHAR(MAX)) FROM [dbo].[TableA]
UNION
SELECT CAST([JSON_Data] AS NVARCHAR(MAX)) FROM [dbo].[TableB]
GO

This works, but it also means more branching and more complexity in reusable SQL utilities. In practice, any metadata-driven script should detect non-comparable source types and auto-cast them to a comparable string/binary representation before applying set operators.

Note: Microsoft already added one json data type column in a new system view sys.external_models in SQL Server 2025. Check this query:

SELECT [o].[name], [c].[name], [tp].[name]
FROM [sys].[system_objects] [o]
	INNER JOIN [sys].[system_columns] [c] ON [c].[object_id] = [o].[object_id]
	INNER JOIN [sys]. [tp] ON [tp].[system_type_id] = [c].[system_type_id] AND [tp].[user_type_id] = [c].[user_type_id]
WHERE [tp].[name] = 'json'