Implications of Nvarchar (50) VS Nvarchar (Max)

Implications of nvarchar (50) vs nvarchar (max)

If you are guaranteed to have strings between 1 and 50 characters, then the same query run across strings of up-to-length X will run faster using varchar(X) vs. varchar(MAX). Additionally, you can't create an index on a varchar(MAX) field.

Once your rows have values above 8000 characters in length, then there are additional performance considerations to contend with (the rows are basically treated as TEXT instead of varchar(n)). Though this isn't terribly relevant as a comparison since there is no varchar(N) option for strings of length over 8000.

Are there any disadvantages to always using nvarchar(MAX)?

Same question was asked on MSDN Forums:

• Varchar(max) vs Varchar(255)

From the original post (much more information there):

When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored...

Does the size used with NVARCHAR matter?

According to the documentation:

nvarchar [ ( n | max ) ]

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. 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.

So, only the actual length of the data entered matters when calculating the storage size.

The documentation doesn't say why it's there, but the length parameter is useful because it enforces simple limit constraints (for example, so someone can't enter 2 GB of text as their "name").

Is Varchar(Max) take much space than Varchar(N) if text length is fixed?

Varchar(MAX) & Varchar(n) reserve fix space for same text store in Column.
I have two table tblMaxData & tblNData and both table have single column with VARCGAR(MAX) & VARCHAR(N). Column have 'Hello' Value in Both Table. After then i was checked space , both column has same occupied Space.

What is the maximum characters for the NVARCHAR(MAX)?

The max size for a column of type NVARCHAR(MAX) is 2 GByte of storage.

Since NVARCHAR uses 2 bytes per character, that's approx. 1 billion characters.

Leo Tolstoj's War and Peace is a 1'440 page book, containing about 600'000 words - so that might be 6 million characters - well rounded up. So you could stick about 166 copies of the entire War and Peace book into each NVARCHAR(MAX) column.

Is that enough space for your needs? :-)

What is the difference between varchar and nvarchar?

An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer. Codepage incompatabilities are a pain, and Unicode is the cure for codepage problems. With cheap disk and memory nowadays, there is really no reason to waste time mucking around with code pages anymore.

All modern operating systems and development platforms use Unicode internally. By using nvarchar rather than varchar, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors is a non-trivial problem.

If you are interfacing with an application that uses only ASCII, I would still recommend using Unicode in the database. The OS and database collation algorithms will work better with Unicode. Unicode avoids conversion problems when interfacing with other systems. And you will be preparing for the future. And you can always validate that your data is restricted to 7-bit ASCII for whatever legacy system you're having to maintain, even while enjoying some of the benefits of full Unicode storage.

Is there a benefit to decreasing the size of my NVARCHAR columns

Setting the number of characters in NVARCHAR is mainly for validation purposes. If there is some reason why you don't want the data to exceed 50 characters then the database will enforce that rule for you by not allowing extra data.

If the total row size exceeds a threshold then it can affect performance, so by restricting the length you could benefit by not allowing your row size to exceed that threshold. But in your case, that does not seem to matter.
The reason for this is that SQL Server can fit more rows onto a Page, which results in less disk I/O and more rows can be stored in memory.

Also, the maximum row size in SQL Server is 8KB as that is the size of a page and rows cannot cross page boundaries. If you insert a row that exceeds 8KB, the extra data will be stored in a row overflow page, which will likely have a negative affect on performance.

---

Related Topics