"Maximum field length 4000 in characters for list and string types"

This still doesn't answer the question as to which way it is stored.

I don't want to define a text field to be 2000 characters if it stores 2000 bytes with every record whether there is data in it or not.  

I am creating a text field called "comments" and I would like to allow a large number of characters for the occasional Shakespeare, but most of the time it would not need to be that big.

That means that most of the time I would be wasting space if behind the scenes the field was set to char(2000) vs. varchar(2000).

So which way is it stored?

In the database, metadata fields have multiple columns they can be stored in depending on the length and type. The "main" columns are as follows:

• nvarchar(4000)
• nvarchar(100)
• decimal(18,5)
• datetime

For text fields, anything 100 or less seems to be stored in both the 4000 and 100 columns, but if it is longer than 100 that column will be NULL and it will only be in the 4000 column.

Anything set as a numeric or date/time would only be stored in the corresponding column.

Dynamic column creation would be a nightmare, so the only practical solution is to include a minimal number of columns that can still accommodate all of the possible field variations.