I'm creating a custom questionnaire and I have a text field in a table. Sometimes it may only store text 15 chars long and sometimes it may store text 4000 characters long.
I want to use an nvarchar() column.
>>the default setting for NVARCHAR(MAX) is to store its text value in the table structure, unless the text is over 8,000 bytes at which point it behaves like an NTEXT and stores the text value in the LOB , and stores a pointer to the text in the table.
Does this only apply if it is defined as nvarchar(max) ? Or can I define it as nvarchar(4000) and still store according to the above?
What would be the best way to define a variable length text field and have it store data the best possible way?