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
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)
Like this:
Like Loading...
Related
SQLZealots 