The data type ntext cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

Exception Message

With today’s post, we will deep dive into an error message that one of my friend encountered recently as below.
The data type ntext cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.
Sample Code

Let us first replicate the issue with the below sample code snippet.
Drop Table  if exists T1,T2
create Table T1(id int,Col1 ntext)
Create Table T2(id int,Col1 ntext)

Insert into T1 Values(1,'Big value')
Insert into T2 Values(1,'Big value')

Select  * From (
Select * From T1
union 
Select * From T2)A

The execution of the above code results in the error message and the message is very clear that those specified operators are not allowed with ntext datatype.

Solution

1. Avoid NTEXT datatype

NTEXT datatype is a deprecated datatype in SQL Server. Try to avoid in future development and replace the existing with nvarchar(max).

2. Replace UNION with UNION ALL (if possible)

UNION ALL is a clear winner of this situation over UNION operator. So, if you can change the code to replace union with union all, that would be the easiest way. Then why is it so? – Simple, UNION operator does a sort operation internally which is forbidden with datatype ntext whereas UNION ALL does not require sort operation.

Select  * From (
Select * From T1
union ALL
Select * From T2)A

3. Construct column list without ntext datatype columns

Drop Table  if exists T1,T2
create Table T1(id int,Col1 ntext)
Create Table T2(id int,Col1 ntext)

Insert into T1 Values(1,'Big value')
Insert into T2 Values(1,'Big value')

--Construction of column list without ntext datatype columns

Declare @Col_list varchar(max)
Set @Col_list = (
SELECT  STUFF((SELECT  ',' + name FROM sys.columns EE
            WHERE   EE.object_id =E.object_id and system_type_id not in (99)
            ORDER BY column_id 
        FOR XML PATH('')), 1, 1, '') AS 'CommaseparatedString'
FROM sys.columns E where object_id = object_id('T1') 
and system_type_id not in (99) group by object_id) 

Declare @sql nvarchar(max) = ''
Set @sql = 'Select  * From (
Select ' + @Col_list + ' From T1
union
Select ' + @Col_list + ' From T2)A'

exec sp_executesql @sql

Sometime, we may not be able to avoid the ntext column depending on the requirement. In such situation, you may use an explicit conversion of those ntext datatype to nvarchar(max) as below.

--Construction of column list with ntext datatype columns but with explicit conversion of datatype

Declare @Col_list varchar(max)
Set @Col_list = (
SELECT  STUFF((SELECT  ',' + case when system_type_id= 99  then 'Cast(' +  name + ' as nvarchar(max)) as ' + name Else name End FROM sys.columns EE
            WHERE   EE.object_id =E.object_id 
            ORDER BY column_id 
        FOR XML PATH('')), 1, 1, '') AS 'CommaseparatedString'
FROM sys.columns E where object_id = object_id('T1') 
group by object_id) 

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s