nvarchar(max) vs NText

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.

The 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 easier to work with nvarchar(max) than text where you had to use WRITETEXT and UPDATETEXT.

Also, text, ntext, etc., are being deprecated (http://msdn.microsoft.com/en-us/library/ms187993.aspx)

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).

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).

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.

Wanted to add my experience with converting. I had many text fields in ancient Linq2SQL code. This was to allow text columns present in indexes to be rebuilt ONLINE.

First I've known about the benefits for years, but always assumed that converting would mean some scary long queries where SQL Server would have to rebuild the table and copy everything over, bringing down my websites and raising my heartrate.

I was also concerned that the Linq2SQL could cause errors if it was doing some kind of verification of the column type.

Happy to report though, that the ALTER commands returned INSTANTLY - so they are definitely only changing table metadata. There may be some offline work happening to bring <8000 character data back to be in-table, but the ALTER command was instant.

I ran the following to find all columns needing conversion:

SELECT concat('ALTER TABLE dbo.[', table_name, '] ALTER COLUMN [', column_name, '] VARCHAR(MAX)'), table_name, column_name
FROM information_schema.columns where data_type = 'TEXT' order by table_name, column_name

SELECT concat('ALTER TABLE dbo.[', table_name, '] ALTER COLUMN [', column_name, '] NVARCHAR(MAX)'), table_name, column_name
FROM information_schema.columns where data_type = 'NTEXT' order by table_name, column_name

This gave me a nice list of queries, which I just selected and copied to a new window. Like I said - running this was instant.

Linq2SQL is pretty ancient - it uses a designer that you drag tables onto. The situation may be more complex for EF Code first but I haven't tackled that yet.

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

You should apparently use nvarchar(max):

MSDN

I want to add that you can use the .WRITE clause for partial or full updates and high performance appends to varchar(max)/nvarchar(max) data types.

Here you can found full example of using .WRITE clause.