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

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan