Did you use the same cast in BOTH queries?

create table tbMasterOption

(

OptionCode nvarchar(10),

OptionDesc nvarchar(50),

OptionLongDesc ntext,

UnitPrice numeric(9, 2)

)

select OptionCode, OptionDesc, CAST(OptionLongDesc AS NVARCHAR(MAX)) as OptionLongDesc, UnitPrice from tbMasterOption

where (RIGHT (OptionCode, 1) = 'E' or RIGHT (Optioncode,1) = 'U') and

OptionDesc like '%%' and

Optiondesc like '%%' and

OptionDesc like '%%'

union

select OptionCode, OptionDesc, CAST(OptionLongDesc AS NVARCHAR(MAX)) as OptionLongDesc, UnitPrice from tbMasterOption

where (RIGHT (OptionCode, 1) = 'E' or RIGHT (Optioncode,1) = 'U') and

OptionLongDesc like '%%' and

OptionLongDesc like '%%' and

OptionLongDesc like '%%'

order by OptionCode

BTW, this query has some serious performance issue potentials. The first one is using a function on a column in your where predicate

. That is nonSARGable and will perform and index scan at best. The second issue is using leading wildcards for OptionDesc. The same issue of nonSARGability applies.