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.

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

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 ([

[To see links please register here]

][1])


[1]:

[To see links please register here]

You should apparently use `nvarchar(max)`:

**[MSDN](

[To see links please register here]

)**

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

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][1]. That will effectively make your schema harder to upgrade when that version of SQL Server will be released.

[1]:

[To see links please register here]

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

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

[Here][2] you can found full example of using `.WRITE` clause.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

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][1].

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.

[![enter image description here][2]][2]

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.


[1]:

[To see links please register here]

[2]: