We help IT Professionals succeed at work.

Nvarchar(max) versus nvarchar(4000)

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.

I read:
>>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?

thanks.
 
Comment
Watch Question

Top Expert 2011

Commented:
WOW, I just think that nvarchar(4000) is way to much to allow users to provide.

However, it is better to use it than nvarchar(MAX).
Starr DuskkASP.NET VB.NET Developer

Author

Commented:
If I have two fields in the table,

	[TextSmall] [nvarchar](255) NULL,
	[TextLarge] [nvarchar](4000) NULL,

Open in new window


And if less than 256, store in the TextSmall field and if more store in the TextLarge field, would this be a better use of storage? When I use the TextSmall for the data, and the TextLarge has only a string.empty stored to it,  would the TextLarge still take up 4000 or would it take up nothing?

thanks.
Top Expert 2011
Commented:
No, I would use just one fieldname and give it nvarchar(4000) unless of course one of your fieldnames, textSmall, is for Summary and textLarge is for detailed info.

Otherwise, one fieldname is enough.

Remember you are using nvarchar (variable text data type).

This means that the box is the size of data entered into it unlike char that is padded with blanks if not filled with specified data length.