I was wondering what would be the consequences of setting NVARCHAR fields to MAX instead of a specific size in SQL Server 2008, and limiting the input from the application's logic. Also, would these be a bad design practice?
Asked
Active
Viewed 7,287 times
6 Answers
7
NVARCHAR(MAX) is a lot better at performing with smaller data than the old NTEXT data type, however, NVARCHAR(n) will always be more efficient in some areas.
As a general rule, using the datatype that best represents the data you are storing is almost always the best practice.
Robin Day
- 100,552
- 23
- 116
- 167
5
There are a lot of performance implications. In particular, in a general purpose string padding scalar UDF, I noticed huge performance differences where the output was declared as VARCHAR(MAX), even though the inputs were never > 40 characters. Changing to VARCHAR(50) made a HUGE improvement.
Cade Roux
- 88,164
- 40
- 182
- 265
3
You shouldn't set all your fields to NVARCHAR(MAX) if you know they will never hold more than a finite number of characters due to the way SQL stores this kind of data - data small enough to fit in the page will be stored in the page, but when it grows too large it will be moved off the page and be stored separately.
Also, are you sure you need NVARCHAR as this stores unicode data which takes up twice the space of standard VARCHAR? If you know you will be using standard characters then use VARCHAR instead.
Slo, think of the uses of your application. If you have an address field that has no theoretical limit on its size, how would you print it on your envelope? You say you will implement logic in the front end applciation, but why still allow the database to have data that is too large? And what happens if data gets into the database that breaks the logic in your front end?
cjk
- 45,739
- 9
- 81
- 112
-
1I do need NVARCHAR, 'exotic' characters are possible. – kjv May 28 '09 at 11:25
-
2+1 for first statement. -1 for second. Even though it is absolutely correct that nvarchar uses twice the space of varchar, being absolutely sure you will only use "standard" characters is a major assumption, easily defeated by, for instance, fields with foreign words like names that contain "strange" characters. I am not sure keeping the assumption pays itself in terms of disk and memory space which is cheaper by the day, comparing to the headache it might bring. – Rui Craveiro May 28 '09 at 11:26
-
I disagree, its wrong to put limitations on the db. The software must always handle the validation and the db has the freedom to grow whenever needed without any changes of the db. – freggel May 28 '09 at 11:58
-
1@freggel - then surely every field should be a VARBINARY(MAX) so that it can hold any data the front end sends to it? – cjk May 28 '09 at 12:12
-
-1 until you motivate the first statement ... due to the way SQL stores this kind of data. NVARCHAR(max) is stored inline until the actual data is too large, in which case it is moved to out-of-line storage. Your point might still hold, but you need to motivate it better. – erikkallen May 28 '09 at 12:49
3
Just to complement all the other answers: be also careful of scenarios where the data can come from other sources, like - just as an example - text files imported from outside your applications; this could bypass any application logic, unless you duplicate it in the import routines...
M.Turrini
- 738
- 1
- 4
- 19
-
Excellent point that many applications developers forget, there are many ways that data gets in a database, all required validation should be done there unless you like data integrity problems. – HLGEM May 28 '09 at 13:16
1
Main drawback is that NVARCHAR(MAX) cannot be indexed with plain indexes.
Also, there are some issues with variables of type NVARCHAR(MAX) and with performance of functions parsing these variables.
If you just want to store and retrieve data as is, and not parse it on SQL Server's side, then NVARCHAR(MAX) is fine.
Quassnoi
- 413,100
- 91
- 616
- 614
