Good practices of SQL Server: nvarchar(max) performance

Question

New .net programmer here. As a new programmer, I always try to follow the best practices that I can when I am working. Today I started with SQL Server, and I asked co-worker which data type should I use for a user description column. He told me to use nvarchar(MAX) and I did and it worked great.

However, should we always use nvarchar(max) for this cases? or is it better to assign something like 500 characters?

I ask because I googled a little bit and I saw people saying that nvarchar(max) reserves a lot of memory for the column, which could reduce the performance of the database eventually.

Edit: Awesome answers guys, I´m clear on the topic now. No unicode stuff, therebefore im gonna go for varchar(600)

Answer 1

Best practice is to perform the appropriate data analysis BEFORE you design your table. Without context, one can assume that a description does not consist of pages and pages of text, so the "max" choice is probably not appropriate. As an additional consideration when choosing varchar(max), remember that you typically need to provide support for displaying such values in an application. If you do not intend to design a GUI to do so, then the choice is probably not appropriate.

And one more caveat - it is generally futile to attempt to future-proof your schema by choosing datatypes that exceed your foreseeable needs.

Answer 2

Beyond not being able to perform an online index rebuild for using a LOB data type, there will be a performance hit for choosing nvarchar(max) instead of nvarchar(4000) or nvarchar(1000).

SQL Server will assume that the average value will be half of the max size, this directly effects the memory that SQL Server will grant for queries.

Aaron Bertrand explains this along with a demo in this presentation/transcript:

So, when SQL Server looks at a column and you’ve decided, “Oh well, we’ll just make this nvarchar 4000 so we’re covered just in case.” SQL Server actually believes that the average value will contain 2000 characters. So, when you have varchar 4000 and it’s vastly oversized and all of the values are 10 characters you’re actually—the memory that SQL Server will grant to this query is 2000 bytes per row, just for that column, instead of the 10 bytes that it really needed. So, you can see how the granted KB goes way up over time and how that actually affects the elapsed time.
- GroupBy.org - T-SQL : Bad Habits and Best Practices - Aaron Bertrand

Reference:

• GroupBy.org - T-SQL : Bad Habits and Best Practices - Aaron Bertrand
• Memory Grants and Data Size - Erik Darling
• Advanced TSQL Tuning: Why Internals Knowledge Matters - Paul White
• Performance comparison of varchar(max) vs. varchar(N) - Remus Rusanu
• Deletes that Split Pages and Forwarded Ghosts - Paul White

Answer 3

You should use nvarchar(max) whenever you have a field that could contain national characters (non-simple ASCII) and could be longer than 8,000 bytes. In that case, it is exactly the right thing.

If you only have simple ASCII, then varchar() is appropriate but nvarchar() does little harm.

If you have a field that has a known maximum length or a reasonable maximum length, then max is not appropriate. So stateName varchar(32) (or whatever), not stateName varchar(max). Or, productDescription nvarchar(255), not productDescription nvarchar(max).

In cases, where the description is long, feel free to use it. But don't over use it.