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.

Solution 1

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)

Solution 2

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

Solution 3

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

Solution 4

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.

Solution 5

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.

View more solutions

Comments

[VoidKing]

What I don't understand is that they say to go with nvarchar(max) but that limits me to 4000 characters. What if I want a field to hold more than that?

[Randy Minder]

nvarchar(max) does not limit you to 4000 characters. You have an unlimited number of characters. Besides, text and ntext have been deprecated by SQL Server. This means that in a future version, they will no longer be supported.

[VoidKing]

OIC, I am using SQL Server CE, which DOES limit my nvarchar(max) to 4000 characters. So for SQL Server Compact, I have no choice but to use ntext in some cases. When they discontinue it, I suppose I will just have to not upgrade some sites.

[Confluence]

SQL Server 2016 apparently will still support them.

[Shiv]

@RandyMinder nvarchar(max) is not unlimited storage. As per SQL Server documentation "max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes."

[RBT]

@Confluence Historically, is text and nText older data type than varchar and nvarchar as far as their existence in SQL Server is concerned?

[aruno]

You don't HAVE to use WRITETEXT and UPDATETEXT. Maybe in 2010 you did though! Interested for any further info.