[RESOLVED] SQL field size concerns

[gwboolean]

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.

[Zvoni]

SQL is not a Database.
do you mean MS SQL Server?

NVARCAR is not a datatype. Do you mean NVARCHAR?

What do you want with a minimum size? If a field has no entry it‘s NULL (if allowed)

[techgnome]

That's why you use a nvarchar and not a nchar field ... If you define a field as nchar(10) ... it's going to take up 10 bytes no matter what size you fill it with... even if it's "Hi" it'll take up the same space as "Hello" ...

nvarchars on the other hand vary... that's what the var means. using nvarchar(10) as an example... It take as much as it needs to UP TO the specified length. So now "Hi" (2) and "Hello" (5) take up as much space as they need to, and nothing more. Now, that's not to suggest everything should be nvarchar(MAX) ... be reasonable ... If you've got a field that can only be so long (let's say StateCode for instance, which can only be 2 characters long) then use nvarchar(2) ... so be reasonable with your lengths ... not everything needs to be a MAX length.


-tg

[gwboolean]

Thanks, that completely eliminates my concerns. Especially good to know that nchar() does not operate the same as nvarchar(). I use MAX only for fields in some tables that a user might want to stuff in a large amount of content. I try to keep that limited. On the other hand, for tables that I use for comboboxes that only contain a few records, I don't worry about how much MAX is.