I'm having trouble understanding how to define a column for my text that has the right size for my max. number of characters. In Oracle I can create a VARCHAR2(10 CHAR) which will be big enough for 10 characters. The size depends on the encoding used in the database. But how do I do that in SQL Server? Do I use varchar(10)? nvarchar(10)? I want to be able to store all kinds of characters (even chinese).
If you want Chinese characters, you need to use nvarchar(n) and specify a length of n that makes sense.
Those are characters you're defining, and the space you need is twice that number (since any Unicode character in SQL Server always uses 2 bytes).
Max. size is nvarchar(4000) - or if you really need more, use nvarchar(max) (for up to 1 billion characters).
I would recommend NOT to just use nvarchar(max) for everything, out of lazyness about considering what size you really need! Since it's a really large column, you won't be able to index it for one.