I am changing my database system from ACCESS to SQL. I am currently setting up the database and have some confusion over field size, for those fields that the size can be set.

I have selected Nvarcar() to use for my text fields. This field can have the size set from a minimum number of characters all the way up to MAX. I am completely unclear on how much memory is set aside, and the amount that can be used.

My understanding of the setting for field size is that for whatever value is placed in the parenthesis, there is a 2 byte (1char) amount of memory set aside for every record created. It is also my understanding that whatever value I place within the parenthesis, up to and including MAX, is the maximum amount of memory that a field can use.

I would like to set some of my text fields to Nvarcar(MAX), but I have been unable to get a number for the minimum memory set aside for this, and what the maximum amount of memory is. I don't mind a record being a memory hog, but I don't want to leave a whole lot of memory on the table that is not being used.