Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Good practices of SQL Server: nvarchar(max) performance

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)

like image 634
Koni Avatar asked May 12 '17 20:05

Koni


People also ask

Does nvarchar Max affect performance?

Strictly speaking the MAX types will always be a bit slower than the non-MAX types, see Performance comparison of varchar(max) vs. varchar(N). But this difference is never visible in practice, where it just becomes noise in the overall performance driven by IO.

Is it good to use nvarchar Max?

You cannot create an index on an nvarchar(MAX) column. You can use full-text indexing, but you cannot create an index on the column to improve query performance. For me, this seals the deal...it is a definite disadvantage to always use nvarchar(MAX).

When should I use nvarchar Max in SQL Server?

If you anticipate data possibly exceeding 4000 character, nvarchar(MAX) is definitely the recommended choice. It is also NOT recomended if your data will never exceed 4000 characters as there are indexing issues.

Is nvarchar max slow?

Conclusion is that in very tight loops the varchar(max) is slower in comparing and assigning when compared with the non-max types. Like all optimizations, it should be only considered and applied after careful measurement reveals that it is a bottleneck.


3 Answers

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.

like image 174
SMor Avatar answered Sep 29 '22 14:09

SMor


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
like image 37
SqlZim Avatar answered Sep 29 '22 12:09

SqlZim


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.

like image 20
Gordon Linoff Avatar answered Sep 29 '22 12:09

Gordon Linoff