>>You definitely need to use nvarchar() and not nchar for 100 characters data. If you don't need to store unicode data, use varchar.
>
>Again, my question was about the impact and not the type of field for its storage.


Nvarchar uses as many chars as enetered.
Char uses as many chars as defined in table design.
Text field uses different place for storage (If I remember well).

So, NVARCHAR is good when you have to store text with very different lengths. This is good for disk space and for better paging of the DB.
CHAR is better when you have to store a text with almost the same length. That improve speed.

Text I dodn't event want to comment :-). That is like MEMO field in VFP.
I'm not sure if this type still exists in SQL Server 2012.
MS warned that "ntext, text, and image data types will be removed in a future version of Microsoft SQL Server".