It's a big question, especially if you don't actually need Unicode for your
storage; for example if you are using only english and you never intent to
store anything else but english. (I won't enter the discussion of storage
for other latin languages such as french. Theoritically, you don't
necessarily need Unicode to store only french or a mix of french and
english. However, there is a big step between the theory and the practice
because of the mix of code pages but this discussion is probably not your
cup of tea.)
The current assumption made by many persons is that the use of Unicode
(nchar, nvarchar and ntext) instead of ASCII will double the size of your
database and cut in a half its performance. However, a lot of stuff stored
inside a database has nothing to do with text and won't be affected by the
use of Unicode. Typically, if you take a database and convert it to
Unicode, you will probably get something like a 30% increase in storage and
a performance decrease of about 10%. Of course, the exact mileage will vary
from database to database but these are the values that I got some years ago
when I made myself these tests.
Now, is this difference really important or not? Well, I would say that in
most cases, if you client is happy with the actual speed but will not be
happy if there is a 10% decrease, then anyway probably that he won't be
happy in a few months when the usual increase on load will happen (for
example by hiring a single new employee). If you can make any use of a
difference of 30% in size and 10% in speed, then probably that you already
in some big trouble anyway. (The only big difference in performance that I
know of is with the use of the operator LIKE with an argument *beginning*
with %, something like LIKE '%....%'. However, if you don't use it or use
it rarely then it's not really a concern at all.).
Think about it, the computer at your front itself is already using Unicode
for practically anything that you are seeing in its GUI. When was the last
time that you have heard someone complaining that the use of ANSI for the
GUI would make his computer going faster? (Maybe your computer is running
to slow at this moment but if this is the case, then it's probably because
you are using Vista instead of WinXP, not because you are using Unicode for
its display.)
One final thought: it's easy to get misguided by performing tests that are
not relevant to your situation. For example, if you have a database of size
20 Megs and don't use with it the operator LIKE '%...%'; then there is no
point to create a 10Gigs database and use it to test the operator LIKE
'%...%' in order to evaluate your own situation.
Finally, it's an error to say that it will be easy to convert a database to
Unicode, especially if its deployement has already be made to more than one
single location (what will you do if you have to manage the design of a
database using ANSI at some places and UNICODE at other places?) or if you
have to perform a lot of validation/steps before its deployment (a change to
Unicode will require that all validation tests to be redone again, as well
as a lot of other things (documentation, backup, shut down, etc.); things
that might not be possible to do.)