nvarchar vs varchar in .net application

[stuckish]

My collegue is saying that you should store text data in Sql Server i
unicode fields (nvarchar) beause that .net (asp.net website) is all
unicode and therefore you save some time not having to convert the data

from ascii to unicode.

So i thought that i should ask you experts what you saying about it?


What is best for performance if you do not have to store any unicode
characters? It's a kind of big table with about 2 million rows.

Br, Ola

Ps. I double posted this in the SQL usegroup to, i want answers from a
diffrent audience since a DB experts and a dotnet experts might have
diffrent views on this .. hope it's ok?

 

[Dave Sexton]

Hi,

Using that logic you could also say that since Unicode characters take up
twice as much space that any parsing operations performed on them would take
twice as long, but I doubt you'll see any performance benefit using one over
the other, to be realistic, unless you're application is constantly
parsing/storing database text at an exorbitant rate. It's most likely going
to be the connection to the database that causes any performance
degradation, especially if you're using pessimistic concurrency.

I say choose the one that fits the data domain and forget about performance.
If you want, you could runs some stress tests using both and see which one
performs best if you think it'll make a difference.

 

[ThunderMusic]

Hi,
I agree with Dave...

Moreover, using unicode will allow you to translate your website at will
when you become ready for it because everything is already stored in unicode
fields. So let's say you have to translate to Spanish or Russian, you will
need to have unicode fields to store your things... Even if you don't
translate... if a user register with a name containing unicode characters
he will be able to enter his real name rather than trying to fin an
equivalent with ascii chars... I'd say stick with unicode unless you find a
REAL BIG problem with it, but I don't will you will find any...

By using unicode, performance won't be a problem unless you process a heavy
load of data and space issue can be resolved at very low costs these days...
so no big problems ahead... But if you use ascii, you will be stuck to
english, french and some other latin languages (maybe not all of them) and
you may have encoding problems between systems such as pc to mac or event on
linux because the extended characters table can change from one country to
another so there's not guarantee your page will look the same everywhere,
but using unicode will...

I hope it helps

ThunderMusic

 

[Patrice]

There is likely much more points that could badly hit performance before
this (still theoricall for now) one. Generally use what makes sense for your
application regardless of theorical considerations (testing would be a first
move, then use unicode if you prove it's significantly faster than varchar).

For now, the very first question is do you want to support unicode for your
website ? If yes use nvarchar, if not use varchar...

 

[Jon Skeet [C# MVP]]

There is likely much more points that could badly hit performance before
this (still theoricall for now) one. Generally use what makes sense for your
application regardless of theorical considerations (testing would be a first
move, then use unicode if you prove it's significantly faster than varchar).

For now, the very first question is do you want to support unicode for your
website ? If yes use nvarchar, if not use varchar...

There's slightly more to it than that: you can support Unicode for the
website but still have an internal product code which would only ever
be in ASCII, for instance.

I would say it's a good idea to use varchar for fields that you
definitely, definitely don't need to contain any non-ASCII data, either
now or in any reasonably forseeable future - but anything else, use
nvarchar.