Re: varchar and nvarchar, which one to use???
From: Erland Sommarskog (esquel_at_sommarskog.se)
Date: 12/26/04
- Next message: Erland Sommarskog: "Re: Query Analyzer: displaying errors immediately"
- Previous message: John Bell: "Re: How to read specific outlook email attachments from SQL"
- In reply to: === Steve L ===: "varchar and nvarchar, which one to use???"
- Next in thread: === Steve L ===: "Re: varchar and nvarchar, which one to use???"
- Reply: === Steve L ===: "Re: varchar and nvarchar, which one to use???"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 26 Dec 2004 11:42:04 +0000 (UTC)
=== Steve L === (steve.lin@powells.com) writes:
> 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!
I'm sorry, if my article confused you, let's see if I can straighten it
out, or at least make you confused at a higher level...
There are several issues at hand here. If we first look at building
SQL Statements, using nvarchar is best practice, since, as Tim S pointed
out, sp_executesql expects its parameters to be nvarchar, and chokes
if you feed it with varchar. It is also a good practice, since if you
are rertieving table or column names dynamically from the system tables,
they may contain all sorts of characters.
Next is issue is for what to use in an application. And here there is
definitely a choice. As Andrew points out, you should think twice before
you move to nvarchar, since nvarchar requires twice the disk space. On
the other hand, you need to think twice before you go with varchar as
well, because if you stay with varchar and then find you need to support
Japanese, Cherokee and Russian in your application, all at the same time,
you are in for a major change to your app with varchar.
For a business like Amazon, the choice should be simple: since their
business is so large, they would run into functional restrictions if they
were to use varchar. On the other hand, the system I work with, is mainly
used in the Nordic countries, and furthermore each installation is often
used in one single country. So we use varchar. (The real reason is that our
system has been around longer than the nvarchar data type.) But the day we
have a customer that requires support for, say, Polish and Swedish the same
database, we would need to rewrite our app. This is because Polish and
Swedish are supported by different code pages in Windows. (If the
requirement would be for Polish and English, we could still use varchar,
since English is supported by about every code page.)
So what type to use, is basically a business decision.
What I discuss in the passage that you quote, are the dangers of mixing
nvarchar and varchar in queries. You can get implicit conversions that
makes indexes useless, and thus ruins your performance.
-- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
- Next message: Erland Sommarskog: "Re: Query Analyzer: displaying errors immediately"
- Previous message: John Bell: "Re: How to read specific outlook email attachments from SQL"
- In reply to: === Steve L ===: "varchar and nvarchar, which one to use???"
- Next in thread: === Steve L ===: "Re: varchar and nvarchar, which one to use???"
- Reply: === Steve L ===: "Re: varchar and nvarchar, which one to use???"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
