Converting Ntext column to Nvarchar(max)

In this short post, I will show you how to convert ntext column to nvarchar(max). Let us consider the following simple temporary table

create table #t(i int, n ntext)
insert into #t(i,n)
select 1,'testing' union all
select 1,'Français' union all
select 1,char(13)+char(10)+'testing'
select * from #t

As the datatype of column ‘n’ is ntext, it is not possible to apply the len() function. So on running the following code

select len(n) from #t

you get the following error

Also it is not possible to apply many string functions directly on the ntext datatype. So let us change the column ‘n’ datatype to nvarchar(max)

alter table #t
alter column n nvarchar(max)
select * from #t

Now it is possible to apply the len() function and string functions too

select len(n) from #t

Both these datatypes store pointer in the row than the actual data. However you can set option to have data in the row also. These points are described well at this Knowledge base.Make sure to read this

http://msdn.microsoft.com/en-US/library/ms189087.aspx