What datatype should I use for my character-based database columns?

Often I see the phrase "I have an NVARCHAR column..." and sometimes have to keep myself from asking "why did you choose that datatype?" Most times, this datatype isn't chosen intentionally; when you upsize from Access to SQL Server, or transfer from other database products such as Sybase SQL Anywhere, this is the default datatype applied to character-based columns (possibly to ensure that any Unicode data stored in such columns would not be lost / corrupted). 
 
When designing your database, you should really try to understand your data, and the datatype that suits it best. Here is an outline of the datatypes available for character-based data: 
 

CHAR	CHAR supports fixed width strings, up to 8,000 characters. Best used when data length is constant, e.g. social security numbers or ISBN numbers.
NCHAR	Similar to CHAR, with the support of Unicode characters. You should only use this datatype if you need Unicode support - due to storage overhead (2 bytes per character means the maximum length of your string is 4,000 characters).
VARCHAR	VARCHAR supports variable length strings, up to 8,000 characters. Best used when data length is variable, e.g. last names or product SKU codes.
NVARCHAR	Similar to VARCHAR, with the support of Unicode characters. You should only use this datatype if you need Unicode support - due to storage overhead (2 bytes per character means the maximum length of your string is 4,000 characters).
TEXT	TEXT supports variable length strings, up to 2 GB, stored off row with a 16-byte pointer in the record itself. Should be used whenever your data will exceed the 8,000 character limit of CHAR/VARCHAR columns (though if you need only 12,000 or even 24,000 characters, you might consider overflowing into multiple VARCHAR columns, rather than use the inflexible and less efficient TEXT datatype).
NTEXT	Similar to TEXT, with the support of Unicode characters. You should only use this datatype if you need Unicode support - due to storage overhead (2 bytes per character means the maximum length of your string is only 1 GB).
VARCHAR(MAX)  SQL 2005 ONLY	Similar to TEXT, however this 2GB capacity is employed much more appropriately... you can now use regular string functions (CHARINDEX, REPLACE, LEFT), and you no longer need WRITETEXT/UPDATETEXT.
NVARCHAR(MAX)  SQL 2005 ONLY	Identical to VARCHAR(MAX) except with half the capacity (due to support of Unicode characters).

 
Of course, choosing TEXT/NTEXT in MSDE limits your options somewhat - if you plan to store 1 or 2 GB in these columns, you can only store a couple at most before exceeding the capacity of the entire database (see Article #2345 for more information). 
 
So, if you inherited nchar/nvarchar/ntext columns from an upsize or import, consider changing those that do not need to support Unicode characters to their non-Unicode datatype equivalents. If you do need to support Unicode strings, make sure you use an N prefix (see KB #239530 for more information): 
 

INSERT fakeTable(nCharColumn) VALUES(N'foo')

 
Access 
 
Access only supports TEXT (255) and MEMO (64 KB when entered through the GUI, and 1 GB when entered programmatically), so the choice here is much easier - use TEXT unless you need more than 255 characters. In addition, if you only need 10 or 20 characters, don't accept the default size (50) as this will be a considerable waste of space.