20

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)

Koni
  • 452
  • 1
  • 7
  • 19
  • Can a description be over 4,000 characters? Is there any limit in how much the UI can display or any enforcement of max length? Do you want to allow up to 2GB entries? – Martin Smith May 12 '17 at 20:04
  • something to think about regarding varchar(max) is that there are some disadvantages, like the fact that you cannot index a varchar(MAX), though you can index a varchar(N). Also, tables with varchar(MAX) cannot use the new ColumnStore index type, which is useful for reporting servers, etc,. http://stackoverflow.com/questions/7141402/why-not-use-varcharmax – Aron May 12 '17 at 20:06
  • @MartinSmith I was thinking about a max of 600-800 characters. – Koni May 12 '17 at 20:16
  • Don't use `max` then. They are less efficient than non max data types and have more limitations. They should only be used when you actually need the extra length. – Martin Smith May 12 '17 at 20:19
  • btw and what about just "varchar" instead of nvarchar? – Koni May 12 '17 at 20:26
  • If you **really need** more than 4000 Unicode characters - then *do use* `nvarchar(max)` - that's what it's there for. But if you **know** from your experience that a column will only have e.g. 25 or 50 characters max (e.g. a phone number), or 255 characters (an e-mail column), then **DO NOT** use `nvarchar(max)` - use the **appropriate datatype** which in this case would be `nvarchar(25)` or whatever length is appropriate for your situation. **YOU** need to know what your data is going to be - design your tables accordingly! – marc_s May 12 '17 at 20:27
  • @MarianoGianni does your data have the potential to contain unicode characters? If so, nvarchar, if not, varchar. – Jacob H May 12 '17 at 20:28
  • `nvarchar` = **Unicode** - 2 bytes per character, max. length therefore `nvarchar(4000)`. `varchar` = **Non-Unicode** - 1 byte per character, therefore the max length is `varchar(8000)` .... – marc_s May 12 '17 at 20:29
  • 1
    Awesome answers guys, I´m clear on the topic now. No unicode stuff, therebefore im gonna go for varchar(600) – Koni May 12 '17 at 20:35
  • No Unicode! Will you also ban typographical quotes? What if someone's work profile includes ⚕? How about this one person's names: 안현수, Виктор Ан, Ahn Hyun-soo? I'm so that I'll have to go work out on my . Obviously, SO doesn't take your approach. – Tom Blodget May 13 '17 at 01:52
  • Possible duplicate of [Are there any disadvantages to always using nvarchar(MAX)?](https://stackoverflow.com/questions/148398/are-there-any-disadvantages-to-always-using-nvarcharmax) – amin Jul 19 '17 at 14:16

3 Answers3

15

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.

SMor
  • 2,830
  • 4
  • 11
  • 14
  • Thanks! in this case, the field would allow the user to set a description about his or her work profile. I was thinking about a max of 600 characters. – Koni May 12 '17 at 20:14
  • @MarianoGianni Yes. These are editorial decisions, not technical. You might also want to limit the number of lines, etc. And, like SO for example, give appropriate feedback and editing capability when over the limit. – Tom Blodget May 13 '17 at 01:41
  • It's not really a question of if you should limit it, but where. Do you want the database giving exceptions after a certain size limit is the question. In practice, for storing large text oriented documents, I've never regretted max. I've regretted less than max many times. Hate to bust the YAGNI bubble, but pretending problems don't happen and being intentionally short sighted isn't a good life strategy. – Jeffrey Vest Jul 08 '20 at 17:05
13

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:

Christian Davén
  • 16,713
  • 12
  • 64
  • 77
SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • 1
    A couple of other links for you http://rusanu.com/2010/03/22/performance-comparison-of-varcharmax-vs-varcharn/ and http://sqlblog.com/blogs/paul_white/archive/2012/08/31/deletes-that-split-pages-and-forwarded-ghosts.aspx – Martin Smith May 12 '17 at 20:33
  • @MartinSmith Added. Thank you! – SqlZim May 12 '17 at 20:42
5

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786