nvarchar(max) vs NText

Question

What are the advantages and disadvantages of using the nvarchar(max) vs. NText data types in SQL Server? I don't need backward compatibility, so it is fine that nvarchar(max) isn't supported in older SQL Server releases.

Edit: Apparently the question also applies to TEXT and IMAGE vs. varchar(max) and varbinary(max), for those searching for those data-types later.

Thanks!

Answer 1

VARCHAR(MAX) is big enough to accommodate TEXT field. TEXT, NTEXT and IMAGE data types of SQL Server 2000 will be deprecated in future version of SQL Server, SQL Server 2005 provides backward compatibility to data types but it is recommended to use new data types which are VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX).

Answer 2

advantages are that you can use functions like LEN and LEFT on nvarchar(max) and you cannot do that against ntext and text. It is also a easier to work with nvarchar(max) than text where you had to use WRITETEXT and UPDATETEXT.

Also text. ntext etc etc are being deprecated

Answer 3

nvarchar(max) is what you want to be using. The biggest advantage is that you can use all the T-SQL string functions on this data type. This is not possible with NText. I'm not aware of any real disadvantages.

Answer 4

The biggest disadvantage of Text (together with NText and Image) is that it will be removed in a future version of SqlServer, as by the documentation. That will effectively make your schema harder to upgrade when that version of SqlServer is released.

Answer 5

You should apparently use nvarchar(max):

http://msdn.microsoft.com/en-us/library/ms178158.aspx

Answer 6

ntext will always store its data in a separate database page, while nvarchar(max) will try to store the data within the database record itself.

So nvarchar(max) is somewhat faster (if you have text that is smaller as 8 kB). I also noticed that the database size will grow slightly slower, this is also good.

Go nvarchar(max).