Prior to Sql Server 2005 it would have been text or nText (the n prefix is for unicode data as opposed to ASCII data) as varchar is limited to approx 8000 chars (the max length of a row allowed and if you use it all for one varchar column you got no space left for anymore) Text fields require a little extra work in your Sql statements though

If you mean Sql Server 2005 Express then your options are better
varchar(max) (non-unicode ASCII chars only)
nvarchar(max) (Unicode chars)
text
nText

I recommend varchar or nvarchar. If your data is all in plain english (no special chracter sets) then varchar(max) is your baby

Does this answer apply to data types in MS SQL Server 7, also?

I'm coming from the world of MS Access and memo data type.

Yep.