varchar and nvarchar, which one to use???

From: === Steve L === (steve.lin_at_powells.com)
Date: 12/24/04 

Date: 24 Dec 2004 14:39:54 -0800

i'm reading the section of an article at this site
<<< http://www.sommarskog.se/dyn-search.html>>>

and i was confused the correct use of varchar and nvarchar.
I've been using varchar in the past, but i noticed all the code samples
use
something like this:

DECLARE @sql nvarchar(4000)
SELECT @sql = 'SELECT * FROM orders'

what's the best practice using these data types?
for a business like amazon.com as an example, what should be used in
the database and in the stored procedures? varchar or nvarchar?

can someone educate me on this in a more straight foward fashion?
thank you!

steve

=== excerpt from article ====
The Northwind database consistently uses the nvarchar data type, but
I've failed to handle this in my procedure. The data in Northwind
appears to be restricted to the characters in Windows Latin-1, which
covers languages such as English, Spanish, French and German, so if you
have a system collation based on this character set, you would never
get any incorrect response from search_orders_2. However, a Russian
user trying:
EXEC search_orders_2 @city = N'Bräcke'
would not get the orders from Folk och Fä HB in Bräcke, because
Bräcke would be converted to varchar as Bracke. This is how my
procedure should have read: (lines 70-71)
SELECT @sql2 = @sql2 + N' AND c.City = N' +
quotename(@city, '''')
And that N which starts an nvarchar literal, should appear with all
nvarchar parameters. (The N stands for National, and is taken from the
ANSI standard SQL-92.)
So why did I not give you the proper code? Well, I figured that many
readers would take the code as a template for their own code, and I
don't want you to include that N by routine. Because, if you use a
nvarchar literal together with an indexed varchar column, the varchar
column will be converted to nvarchar, and that precludes use of the
index. Thus, you would get the right result, but performance would be
poor.

Relevant Pages

  • Re: Data Type nvarchar or varchar
    ... Access with Unicode, why would you want to start changing things now? ... Do you think can i change nvarchar to varchar without impact problems? ... can switch the data types back to varchar. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: varchar and nvarchar, which one to use???
    ... > and i was confused the correct use of varchar and nvarchar. ... For a business like Amazon, the choice should be simple: ... have a customer that requires support for, say, Polish and Swedish the same ...
    (microsoft.public.sqlserver.programming)
  • Re: varchar and nvarchar, which one to use???
    ... If you will only deal with the normal ascii characters ... languages and must store a character that requires two bytes to store ... and i was confused the correct use of varchar and nvarchar. ... I've been using varchar in the past, but i noticed all the code samples ...
    (microsoft.public.sqlserver.programming)
  • Re: Change Data Type
    ... The issues with varchar and nvarchar are fairly straight-forward, ... ALTER TABLE cannot change texts and ntexts. ...
    (microsoft.public.sqlserver.server)
  • Re: VarChar VS. nVarChar
    ... strongly advises using the least amount of storage in a row. ... Unless I was working on a really memory constrained system I would use ... So far I have never used varchar other than when I needed the ... Go with nvarchar unless there is a real reason not to. ...
    (microsoft.public.dotnet.framework.adonet)