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?
+2
A:
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?
ck
2009-05-28 11:18:28
I do need NVARCHAR, 'exotic' characters are possible.
iulianchira
2009-05-28 11:25:00
+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
2009-05-28 11:26:57
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
2009-05-28 11:58:52
@freggel - then surely every field should be a VARBINARY(MAX) so that it can hold any data the front end sends to it?
ck
2009-05-28 12:12:14
-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
2009-05-28 12:49:50
@all : answer updated.
ck
2009-05-28 12:56:10
... and downvote removed
erikkallen
2009-05-28 12:58:36
+3
A:
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
2009-05-28 11:21:52
+1
A:
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
2009-05-28 11:24:16
+4
A:
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
2009-05-28 11:27:04
A:
Seting a limit has a side effect of some validation on the max length
Pbearne
2009-05-28 11:36:46
+2
A:
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...
Turro
2009-05-28 12:30:11
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
2009-05-28 13:16:44